Free Microsoft Excel 2013 Quick Reference

# Highlight (special mark) cell if number is above a specified amount

I have a spreadsheet I use to keep track of numbers. And in the column furthest to the right, it calculates the percentage difference between the previous 2 cells.

So for example:

Cell A1 = 100
Cell B1 = 150
Cell C1 = 50%

Cell A2 = 50
Cell B2 = 55
Cell C2 = 10%

I am looking for a way to highlight (or put some other type of special mark) on the percentage cells if they go above a certain percentage number. So for example, if I set the maxmimum amount to 20%, Cell C1 would highlight in Red and Cell C2 would stay normal.

Is there a way to do this ? I am hoping there is a simple formula I can use rather than have to use Array's or any other kind of function.

Thanks,
H.F.

## Related Results

### Calculate cell if number [not text]

I need to carry out some calculations on over 50,000 cells. 'Each record' consists of a unique reference, followed by 10 cells [columns] which either contain numbers or text. I wish to change only the cells containing numbers. Is there a non VBA way to do this?
David

### Changing a cell to number format using a formula

Hello,

I apologize if this is a very basic question but I am writing a template to analyze some data and I am having trouble with the formatting of my cells. I am extracting numerical values from other sheets in the workbook but I believe that Excel is not treating them as numbers. For instance, when I evaluate a formula is giving a False response to "1"<9. I believe that I can remedy this problem if I can adjust the format of the cells to 'numbers'. Is there a way to do this using formulae, or am I forced to resort to a macro?

Thank you very much for your time.

Paul

### Formula to find numbers that sum to a specified amount

Is there any way you can create a formula that would look through a bunch of numbers to see if the SUM of any combination of those numbers equals x (a specified amount)?

For example, I have a column of numbers Rows 1-250, and I want to see if the SUM of any of those numbers equals a specified amount if added together. It could be 2 numbers added together or could even be more than 2. I would be specifying the amount manually. For instance, I want to see if any of the set of numbers' added together would equal 5,555.23. Would it be able to spit out the exact numbers that make up this specified amount?

Thanks!

### Highlight Cell If Number is Between Certain Range

Ive been trying to figure this out for a couple days with no luck. I have searched all over the internet but cannot find an answer.

I have a series of columns, and at the bottom of each column I have a cell (which ill call the Total Cell) which automatically adds up the 10 or so cells above it.

What I am trying to do is create a function so that if one of the Total Cells amount drops into the -1000 range Excel will highlight that particular cell or give an error or do something to draw attention to it.

Any help would be greatly appreciated.

Thanks

### Shade cell if number is greater than zero

I have a list of cells that contain numbers and want to know if there is a way to have the worksheet check each cell and highlight the cell if it has a greater value than zero. I am using Excel 2007

A1 - 0
A2 - 3
A3 - 1
A4 - 0
A5 - 0
A6 - 9

### Count Cells By Number & Add Adjacent Cell If Number Is X

Hi, i need to:

Create some sort of formula combination or macro that will:

Recognise a cell with a value of 1, 2 or 3 in. If 3 is in the cell, the cell to its left will be counted and added to a total. If the cell that has 3 in changes the value is removed from the total.

Ive tried lots of methods but i cant figure this one out! :S help!

thanks!

### Delete cell if same as above

I would like to delete the contents of a cell if it is exactly the same as the cell above it. How can I do that? It would just be on a range of cells in one column. I don't want to move anything. Thanks for your help.

### Checking one cell, if number is a range then this

I need help entering a formula and have no idea how to do it. Thanks in
advance. I want to calculate how many batches of batter I need for baking
certain sized cakes.

I have one column that calculates the total cups of batter I need per pan
size. If that number is less than or equal to 7 I need it to display "1", if
it's 7.01 to 14 I need it to display "2", if it's 14.01 to 21 I need it
display "3'.

### How Do I Check To See If A Number Is In A Certain Format?

I'm trying to find a way to determine if a number in a cell is formatted in a certain way or not.

I'm trying to find out if the number is in a 00000.00 format (for example 12345.67) or not.

I'm thinking that there must be some way to either check to see if the number is in that format, or check to see if the third character starting from the right is a ".", or checking to see if the number contains a "." at all. But I don't know how to do any of those things.

Any help?

### Highlight Cells if Value Equal to a Certain Amount on Subtotal Lines

All,

I'm trying to highlight only the cells on the subtotal lines of a spreadsheet if the value is equal to 56. Is this possible with a macro? Conditional formating won't work, excel keeps returning a message saying the selection is too complex or it won't retain some of the formats when I go to save the spreadsheet. I have about 25k lines after the subtotaling is applied.

Please keep in mind that I'm a rookie when it comes to macros.

Thanks
Jeff

### Change character color within a cell if it is not a specific RGB

Hi,

Is there a way to loop each cell in a selection and to change all characters within the selection that are any shade/tint of blue other than RBG (0,0,255) and change the character font color to RGB (0,0,255)?
I wrote the following code, but it doesn't seem to work.
```
VB:
TrueBlueTextCorrection()
'For any character in a selection that is no "true blue" RGB (0,0,255) then it will be made true blue
Dim Cell As Range
Dim i As Long
LastRow = Cells.Find(What:="*", after:=[A1], searchdirection:=xlPrevious).Select

For Each Cell In Selection
With Cell
For i = 1 To Len(Cell)
Select Case Cell.Characters(i, 1).Font.Color
Case RGB(0, 0, 0)
Cells.Characters(i, 1).Font.Color = RGB(0, 0, 0)

Select Case Cell.Characters(i, 1).Font.Color
Case RGB(0, 102, 255), RGB(197, 217, 241), RGB(141, 180, 226), RGB(83, 141, 213), RGB(22, 54, 92), _
RGB(15, 36, 62), RGB(79, 129, 189), RGB(220, 230, 241), RGB(184, 204, 228), RGB(54, 96, 146), _
RGB(36, 64, 98), RGB(0, 176, 240), RGB(0, 112, 192), RGB(0, 32, 96), RGB(0, 51, 153), RGB(0, 0, 204),
_
RGB(0, 51, 204), RGB(51, 51, 255), RGB(0, 153, 255), RGB(51, 102, 255), RGB(51, 51, 204), _
RGB(75, 172, 198), RGB(218, 238, 243), RGB(183, 222, 232), RGB(146, 205, 220), RGB(49, 134, 155), _
RGB(33, 89, 103)
Cells.Characters(i, 1).Font.Color = RGB(0, 0, 255)
End Select
End Select
Next i
End With
Next Cell

End Sub

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

```
Thanks!

### VBA : Lock specific cell if data is in a separate r

Hi All,

Here's what I'm trying to accomplish. If there is any value in a particular range of cells, I'd like to lock a separate cell that is not in the range. For instance:
If there is any value in the cells in the range A1:A10, I would like to lock cell B1 so that it cannot be modified. Make sense??

I'd appreciate any input.
Thanks!
JL

### How to show a cell/data separately if the number goes below a specified level.?

Dear Frnds,

I have entered a formula in a cell which changes its figures(in numbers) automatically.

There are around 70 cells in that workbook, whose numbers increases or decreases automatically.

I want to show all those cells or headings whose value goes below a specified level.

example. Suppose there are 20 cells from A1 to T1. Each cell has some addition or subtraction formula in it. When ever any number in those cell goes below 500 then it should appear separately.
So that the user without going through all the cells come to know that this cell number is below the specified limit.

I hope I am able to make my question / problem clear to you people.

Kindly give me the solution to this.

I will be thank ful to you.

jackyhercules@yahoo.com

### If number is 2 then font = red, if number is 3 then font = blue

If number is 2 then font = red, if number is 3 then font = blue

Is there a way to do this in Excel?

Probably using conditional formatting but I'm not sure how.

### If number is within certain range Display ____

I am having a problem coming up with a formula to perform a calculation if one of the numbers is within a certain range. Here is what i need...

If the number is between 0.90 & 1.10 then i need it to say "YES" but, if the number is greater than or equal to 1.11 then i need it to say "NO", if its less than or equal to 0.89 i need it to say "NO". but if the number = 0 i need it to say BLANK. There will never be a negative number. Here is what i have that does the range, but i cant figure out how to make it say if it is = 0 then make it say "BLANK"

=IF(OR(N7>=1.11,N7<=0.89),"NO","YES")

Any ideas?

### How to change colour of value in cell if there is more than 0?

How to change colour of value in cell if there is value more than 0?

### Want to return a value based on a whether a number is within a ra.

Want to return a value based on a whether a number is within a range of
numbers using the IF function. Can someone please give me the correct syntax.

### Multiple Greater Than Less Than

Hi, I am VERY new to this, so please go easy on me.

I want to calculate a mark-up on one cell.
If it is under a set amount then I want the mark-up to change.
If it is then over a set amount but below another, then I want the mark up to change again, etc...

I have got this far (eg. below) but the calculation does not work properly
when the value in "A3" is over "1"

=IF(A3=0.5,A3/0.25,IF(A3>=1,A3/0.35,IF(A3>=2,A3/0.5,0))))

Am I on the wong track?

Ps. can I have more than 7 formulas in this case?

### Check to see if a time is within a specified range

Is there a way to check to see if a time is within a specified range by using a calculation. For example:

Time Scheduled Time Performed
3:00 3:07

So I want to created a formula where I can check that the time performed is within plus/minus 15 min of the time schduled. I have over 500 times to check and will continue to have more and the would make things a lot simpler if I could make Excel check the times. I know I can use (.25/24) to equal 15 minutes, but I don't know how to say plus or minus or if that's even possible. The +/- symbol does not work. It returns an error.

Thanks

### Remove legend if value is N/A

Hi all!
Here to scoop up some of your help again =) =P

I have a bar chart and a lot of it is blank because a lot of the values i have are n/a (so zero wont show) is there a way to prevent the cart dfrom displaying data if it is n/a or 0? maybe a macro of some sort?

Smar question on MR.Excel

Cheers yall!

### Multiplying one cell with another and getting a dollar amount

I for the life of me, cannot figure out how to do this. All I want to do is multiply one cell with another, and have a cell display the total in a dollar amount.

Example:

\$0.07 (Cell A)
X's
200 (Cell B)
Total \$ (Cell C)

Thank you so much for any help with this!

### VBA : Select a cell if it is a number

I have a straight forward keystroke macro which opens a new bank balance worksheet (from a template) on the 5th of each month. One of the received credits occurs every 28 days on a Friday(row 11), cell B11 shows this date. B11 in the template has the number 28 already entered, using copy/paste special add, the B11 date from the Feb worksheet when pasted to the March worksheet shows a date 28 days forward. 6 Feb shows as 5 Mar when copied and pasted. 28 days forward from 5 Mar is 2 Apr, therefore both these dates show in the March balance worksheet (closes 5th April). The 2 Apr shows in B12, it is this cell that needs to be copied (not B11) next month. I need to add to the macro something like "if(isnumber(b12),Range("B12").PasteSpecial"...etc,(otherwise)"Range("B11").PasteSpecial"...etc If there is only one date showing B12 shows Text.

### Conditional Format Cell If Numbers Appear x Times

I would like the B6 cell to highlight if there are 4, 5 or 6 matches in the C6:H6 columns. Each Name (B) and their 6 numbers (C:H) are seperate.

The numbers in the C:H columns are already set up to highlight when they match any numbers in the C2:H4.

If possible I would like 3 different colors. One for 4 matches, one for 5 & and one for 6.

I have tried to figure out how to try and write the function without any sucess.

Any help is appreciated.

### Moving cells if there is a duplicate entry

HI,

I have a colum holding an account number and a colum holding an amount.

If there is a duplicate account number below (they are sorted into order), then i want to move the amount to the next cell above to one of the account numbers and then delete the other account number. There are values in Cols ABC which need to remain in the same place.

example

Column D****Column E
123********10
456********20
456********30
456********50
897********100

The result i would need is:

Column D****ColE***ColF***ColG
123********10
456********20*****30****50
897********100

Hope that makes sense.

I have tried the following but doesnt seem to work-

Dim Rng As Range, _
LstRw As Long, _
i As Long
Application.ScreenUpdating = False
LstRw = Cells(Rows.Count, 4).End(xlUp).Row
Set Rng = Range(Cells(1, 4), Cells(LstRw, 4))

For i = LstRw To 1 Step -1
If Application.WorksheetFunction.CountIf(Rng, Cells(i, 4)) > 1 Then _
Range(Cells(i, 5), Cells(i, 10)).Select
Selection.Cut
ActiveCell.Offset(-1, 0).Select
Selection.End(xlToRight).Select
ActiveCell.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Cells(i, 4).Delete

Next i

Application.ScreenUpdating = True

Any ideas?

Cheers,

Matt