Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlight cells greater than zero
I need to write a macro where all cells within a specified column are
highlighted in yellow if they have a value greater than zero. Can anyone please help with this? -- Thank you, Jodie |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlight cells greater than zero
I would prefer to use conditional formatting instead of code. Look up
Conditional Formatting in the help section it should help you. If you must use code you can use this: Sub HighlightCells() Dim rng As Range For Each rng In Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row) If rng.Value 0 Then rng.Interior.Color = vbYellow End If Next rng End Sub Hope this helps! If so, let me know, click "YES" below. -- Cheers, Ryan "Jodie" wrote: I need to write a macro where all cells within a specified column are highlighted in yellow if they have a value greater than zero. Can anyone please help with this? -- Thank you, Jodie |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlight cells greater than zero
Jodie,
You don't need a macro for this, conditional formatting is a better approach but if it's a macro you want try this Sub Shade_Cells() Dim LastRow As Long LastRow = Cells(Cells.Rows.count, "A").End(xlUp).Row Set MyRange = Range("A1:A" & LastRow) For Each c In MyRange If IsNumeric(c.Value) And c.Value 0 Then c.Interior.ColorIndex = 6 End If Next End Sub -- Mike When competing hypotheses are equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. Occam''s razor (Abbrev) "Jodie" wrote: I need to write a macro where all cells within a specified column are highlighted in yellow if they have a value greater than zero. Can anyone please help with this? -- Thank you, Jodie |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlight cells greater than zero
Ryan,
I suggest you check for a number also because if there's text in the range that too will evaluate as 0. -- Mike When competing hypotheses are equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. Occam''s razor (Abbrev) "Ryan H" wrote: I would prefer to use conditional formatting instead of code. Look up Conditional Formatting in the help section it should help you. If you must use code you can use this: Sub HighlightCells() Dim rng As Range For Each rng In Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row) If rng.Value 0 Then rng.Interior.Color = vbYellow End If Next rng End Sub Hope this helps! If so, let me know, click "YES" below. -- Cheers, Ryan "Jodie" wrote: I need to write a macro where all cells within a specified column are highlighted in yellow if they have a value greater than zero. Can anyone please help with this? -- Thank you, Jodie |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlight cells greater than zero
I thank you both. I am not familiar with conditional formatting. I am
trying to include this highlighting in a macro that I have written to do other things in the same spreadsheet. Is this something that could be done with conditional highlighting? -- Thank you, Jodie "Mike H" wrote: Jodie, You don't need a macro for this, conditional formatting is a better approach but if it's a macro you want try this Sub Shade_Cells() Dim LastRow As Long LastRow = Cells(Cells.Rows.count, "A").End(xlUp).Row Set MyRange = Range("A1:A" & LastRow) For Each c In MyRange If IsNumeric(c.Value) And c.Value 0 Then c.Interior.ColorIndex = 6 End If Next End Sub -- Mike When competing hypotheses are equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. Occam''s razor (Abbrev) "Jodie" wrote: I need to write a macro where all cells within a specified column are highlighted in yellow if they have a value greater than zero. Can anyone please help with this? -- Thank you, Jodie |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlight cells greater than zero
Jodie,
the problem with doing it with a macro is that when the cell is shaded then shaded it remains. If the value in the cell changes to less than zero the colour remains. Conditional formatting is different, here Excel monitors the value based upon the conditional format you set so lets see how to do it. I don't know your version of Excel so I assume E2003. Select A1 and try this Click Format|Conditional formatting Formula is and enter the formula =A10 Pattern tab and choose a colour OK Now try entering values in a1 and see the colour change. You can use the format painter to copy this CF into multiple cells -- Mike When competing hypotheses are equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. Occam''s razor (Abbrev) "Jodie" wrote: I thank you both. I am not familiar with conditional formatting. I am trying to include this highlighting in a macro that I have written to do other things in the same spreadsheet. Is this something that could be done with conditional highlighting? -- Thank you, Jodie "Mike H" wrote: Jodie, You don't need a macro for this, conditional formatting is a better approach but if it's a macro you want try this Sub Shade_Cells() Dim LastRow As Long LastRow = Cells(Cells.Rows.count, "A").End(xlUp).Row Set MyRange = Range("A1:A" & LastRow) For Each c In MyRange If IsNumeric(c.Value) And c.Value 0 Then c.Interior.ColorIndex = 6 End If Next End Sub -- Mike When competing hypotheses are equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. Occam''s razor (Abbrev) "Jodie" wrote: I need to write a macro where all cells within a specified column are highlighted in yellow if they have a value greater than zero. Can anyone please help with this? -- Thank you, Jodie |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Highlight cells greater than zero
Good to know. Thanks!
-- Thank you, Jodie "Mike H" wrote: Jodie, the problem with doing it with a macro is that when the cell is shaded then shaded it remains. If the value in the cell changes to less than zero the colour remains. Conditional formatting is different, here Excel monitors the value based upon the conditional format you set so lets see how to do it. I don't know your version of Excel so I assume E2003. Select A1 and try this Click Format|Conditional formatting Formula is and enter the formula =A10 Pattern tab and choose a colour OK Now try entering values in a1 and see the colour change. You can use the format painter to copy this CF into multiple cells -- Mike When competing hypotheses are equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. Occam''s razor (Abbrev) "Jodie" wrote: I thank you both. I am not familiar with conditional formatting. I am trying to include this highlighting in a macro that I have written to do other things in the same spreadsheet. Is this something that could be done with conditional highlighting? -- Thank you, Jodie "Mike H" wrote: Jodie, You don't need a macro for this, conditional formatting is a better approach but if it's a macro you want try this Sub Shade_Cells() Dim LastRow As Long LastRow = Cells(Cells.Rows.count, "A").End(xlUp).Row Set MyRange = Range("A1:A" & LastRow) For Each c In MyRange If IsNumeric(c.Value) And c.Value 0 Then c.Interior.ColorIndex = 6 End If Next End Sub -- Mike When competing hypotheses are equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. Occam''s razor (Abbrev) "Jodie" wrote: I need to write a macro where all cells within a specified column are highlighted in yellow if they have a value greater than zero. Can anyone please help with this? -- Thank you, Jodie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
want to highlight cell which having comment & greater than 0 | Excel Discussion (Misc queries) | |||
Highlight MAX Cell Greater Than Zero | Excel Discussion (Misc queries) | |||
how to highlight more related cells if cell highlight | Excel Discussion (Misc queries) | |||
Greater of Two Cells | Excel Worksheet Functions | |||
Highlight cells with ctrl-click but only un-highlight one cell | Excel Discussion (Misc queries) |