highlighting cells above a certain number

[Lyn Radnell]

Hi,

Can anyone help me?

Out of a group of cells, I would like to highlight any cells over the the number 15 and any highlight in a different colour lower than the number 2

Appreciate any help.

Lyn

 

[Peter_SSs]

Hi,

Can anyone help me?

Out of a group of cells, I would like to highlight any cells over the the number 15 and any highlight in a different colour lower than the number 2

Appreciate any help.

Lyn

You'll need to use Conditional Formatting. Here's some help:
http://www.contextures.com/xlCondFormat01.html

If you need more specific direction, then post back with
- what version of Excel you are using
- what the range of cells you are dealing with is
- are there (or could there be) any blank cells in the range?

 

[Jaye7]

The following is an example code for you, just paste it into the worksheet event.

Private Sub Worksheet_Change(ByVal Target As Range)
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="="""""""""""""
    Selection.FormatConditions(1).Font.ColorIndex = xlAutomatic
    Selection.FormatConditions(1).Interior.Pattern = xlNone
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _
        Formula1:="2"
    Selection.FormatConditions(2).Interior.ColorIndex = 6
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual _
        , Formula1:="15"
    Selection.FormatConditions(3).Interior.ColorIndex = 3
End Sub

 

[Lyn Radnell]

Hi Peter,

Things are so simple when you know how

Thanks for your help

Cheers from Victoria

Lyn

 

[Peter_SSs]

The following is an example code for you, just paste it into the worksheet event.

Private Sub Worksheet_Change(ByVal Target As Range)
    Selection.FormatConditions.Delete
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
        Formula1:="="""""""""""""
    Selection.FormatConditions(1).Font.ColorIndex = xlAutomatic
    Selection.FormatConditions(1).Interior.Pattern = xlNone
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLessEqual, _
        Formula1:="2"
    Selection.FormatConditions(2).Interior.ColorIndex = 6
    Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual _
        , Formula1:="15"
    Selection.FormatConditions(3).Interior.ColorIndex = 3
End Sub

That code can have some strange results.

 

[dadomi]

How can I apply this macro to just Columns D and H?

 

[Peter_SSs]

How can I apply this macro to just Columns D and H?

Welcome to the MrExcel board!

1. Why do you need to do this with code? Why not just manually apply the Conditional Formatting to columns D and H?

If you do need to use code, I suggest that you probably don't want the code above. It was supposed to colour entries >=15 red and entries <=2 yellow. However, as I mentioned previously it can produce some unexpected results.

To get the best suggestion, we need a bit more information. For example,

2. Do columns D and H contain any formulas or will the values always be entered manually?

3. What version of Excel are you using?

4. Is it >=15 and <=2 that you want coloured? If not, more details please.

5. What do you want to happen to the cell colour if the cell is empty (since this could be considered as less than 2)?

 

[dadomi]

Welcome to the MrExcel board!
Thank you Peter! Thank you for replying to my response as I have been searching the forums for help.

1. Why do you need to do this with code? Why not just manually apply the Conditional Formatting to columns D and H?
I tried to use Conditional Formatting but when cut and pasting on to a new sheet (same workbook) the Conditional Formatting did not hold. I noticed some highlighted cells were moved up a cell or down a cell.
If you do need to use code, I suggest that you probably don't want the code above. It was supposed to colour entries >=15 red and entries <=2 yellow. However, as I mentioned previously it can produce some unexpected results.

To get the best suggestion, we need a bit more information. For example,

2. Do columns D and H contain any formulas or will the values always be entered manually?
Actually, I have Columns D, E, and H that need to be filtered. These columns will randomly have date entered here. Here are my specifics:
Cells should be highlighted if:
Column D: blank, or greater than 9999999, or contain letters/words
Column E: blank, or contain letters or words
Column H: blank, less than 200000000, greater than 999999999, or contain letters or words.
3. What version of Excel are you using?
I am using MS Excel 2003.
4. Is it >=15 and <=2 that you want coloured? If not, more details please.

5. What do you want to happen to the cell colour if the cell is empty (since this could be considered as less than 2)?[/QUOTE]
A normal yellow highlight should be fine for any of the findings above.

Thanks for your help in advance.
dadomi

 

[Peter_SSs]

Try this in a fresh worksheet.

1. Select D1:Dx (or whatever range in column D you want the Conditional Formatting to apply to. Even the whole column if you really want, though I wouldn't recommend that since CF can slow your sheet performance if you have too much of it).

2. Apply the CF shown below for D1. (If your range does not start in row 1 then just ensure that where I have D1, you put whatever the active cell in your column D selection is)

3. Repeat the above steps for columns E and H.

If the results below are not what you were trying to describe, then please elaborate.

You should be able to copy any of these cells/ranges to another locatuion and the CF should 'hold'.

Excel Workbook
	D	E	F	G	H
1	25	25			25
2	d	d			d
3	999999	999999			999999
4	cat dog	cat dog			cat dog
5	10000000	10000000			10000000
6	16	16			16
7	16.2856	16.2856			16.2856
8
9	200001000	200001000			200001000
10
dadomi CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D1	1. / Formula is =OR(D1="",D1>9999999)	Abc
E1	1. / Formula is =NOT(ISNUMBER(E1))	Abc
H1	1. / Formula is =OR(H1<200000000,H1>999999999)	Abc

 

[dadomi]

Thank you Peter! It works great. But do you know how I can copy or cut any rows consisting of any highlighted cells on to a new sheet within the same workbook?