Highlight cells greater than zero

  • Thread starter Thread starter Jodie
  • Start date Start date
J

Jodie

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?
 
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.
 
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)
 
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)
 
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?
 
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
=A1>0
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)
 
Good to know. Thanks!
--
Thank you, Jodie


Mike H said:
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
=A1>0
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)
Click to expand...
 
Back
Top