?
Solved

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

Posted on 2011-04-28
6
Medium Priority
?
386 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:gvilbis
  • 4
6 Comments
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 1600 total points
ID: 35482210
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
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 400 total points
ID: 35482227
You could also just use conditional formatting which is a lot simpler.
0
 
LVL 30

Assisted Solution

by:SiddharthRout
SiddharthRout earned 1600 total points
ID: 35482236
Yeah I agree but he wanted it via macro. This is further in continuation to his previous request :)

Sid
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:gvilbis
ID: 35489528
ok and how do i set conditional formatting?

thanks
0
 
LVL 30

Assisted Solution

by:SiddharthRout
SiddharthRout earned 1600 total points
ID: 35489625
Which Excel version do you have?

Sid
0
 
LVL 30

Assisted Solution

by:SiddharthRout
SiddharthRout earned 1600 total points
ID: 35489890
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question