what is the macro code for coloring collumn B in red if the formula value is greater then zero?

what is the macro code for coloring collumn B in red if the formula value is greater then zero?
i would like to check each cell in collumn B if it greater zero then color the cell to red

Thanks
gvilbisAsked:
Who is Participating?
 
SiddharthRoutCommented:
See the sample file attached.

Sid

Code Used

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    
    On Error GoTo Whoa
    
    If Not Intersect(Target, Columns(2)) Is Nothing Then
        Select Case Target.Value
        
        Case Is > 0
            Target.Interior.ColorIndex = 3
        Case Else
            Target.Interior.Pattern = xlNone
        End Select
    End If
LetsContinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

Open in new window

Color-Cells.xls
0
 
Rory ArchibaldCommented:
You could also just use conditional formatting which is a lot simpler.
0
 
SiddharthRoutCommented:
Yeah I agree but he wanted it via macro. This is further in continuation to his previous request :)

Sid
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
gvilbisAuthor Commented:
ok and how do i set conditional formatting?

thanks
0
 
SiddharthRoutCommented:
Which Excel version do you have?

Sid
0
 
SiddharthRoutCommented:
IF

Excel 2003
See this small tutorial, that I wrote few years ago.
http://www.vbforums.com/showthread.php?t=453299

Use this as a formula

=$B1>0

Excel 2007

Select Col B
Click on the "Home" Tab
Select Conditional Formatting
Click on New Rule
Select "Use a Formula to determine which cells to format"
In the text box for formula type,
=$B1>0
Click the format button and under the Tab 'Fill', Select the red color.
Click Ok
Click Ok again
And you are done.

Excel 2010
Same procedure as 2007.

HTH

Sid
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.