• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 337
  • Last Modified:

VBA Code

I need a macro for excel code that would turn the positive numbers in specified columns to red.

Thanks,
Paul
0
pauledwardian
Asked:
pauledwardian
  • 5
  • 4
  • 4
1 Solution
 
Pratima PharandeCommented:
if ( Range("A1").Value >0)
Range("A1").Interior.ColorIndex = 19
0
 
Chris BottomleyCommented:
Have you considered selecting those cells and simply changing the format via the right click cell format
to:
#,#;[Red]-#,#

Chris
0
 
Chris BottomleyCommented:
As for a macro then the following will process columns A & C only:

Chris
Sub redSome()
Dim rng As Range
Dim cel As Range

    Set rng = Intersect(ActiveSheet.UsedRange, Range("A:A, C:C"))
    For Each cel In rng
        If cel < 0 Then
            cel.Font.Color = vbRed
        Else
            cel.Font.Color = 0
        End If
    Next
End Sub

Open in new window

0
Independent Software Vendors: 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!

 
pauledwardianAuthor Commented:
pratima;


It gives me an error. It says Compile Error Syntax Error
0
 
Pratima PharandeCommented:
if ( Range("A1").Value >0)  Then
Range("A1").Interior.ColorIndex = 19

End If
0
 
Pratima PharandeCommented:
Sub test()
For Each Cell In Range("a1:z100")
If Cell.Value > 0 Then
    Cell.Interior.ColorIndex = 19

End If
Next Cell
End Sub

change the color index of Red to 19
0
 
pauledwardianAuthor Commented:
Thanks Pratima, can you modify it so I can select ranges from for example A1 trough F20?
0
 
Pratima PharandeCommented:
Sub test()
For Each Cell In Range("a1:F20")
If Cell.Value > 0 Then
    Cell.Interior.ColorIndex = 19

End If
Next Cell
End Sub
0
 
pauledwardianAuthor Commented:
Never mind that worked perfect. Thanks a lot.
0
 
pauledwardianAuthor Commented:
Fast reply and great solution.
0
 
Chris BottomleyCommented:
For cells rather than whole columns then use:

Chris
ub redSome()
Dim rng As Range
Dim cel As Range

    Set rng = Intersect(ActiveSheet.UsedRange, Range("A1:A20, C1:C22"))
    For Each cel In rng
        If cel < 0 Then
            cel.Font.Color = 0
        Else
            cel.Font.Color = vbRed
        End If
    Next
End Sub

Open in new window

0
 
Chris BottomleyCommented:
I appreciate you have accepted an answer, I also appreciate I got the sense wrong but I still supply a corrected sense solution below since as it stands the code will set the red but ignore corrections for a change in polarity which my original solution addressed.

i.e. if you change a red number to negative the accepted solution will not change the color, it may help you in the future therefore.

Chris
Sub redSome()
Dim rng As Range
Dim cel As Range

    Set rng = Intersect(ActiveSheet.UsedRange, Range("A1:A20, C1:C22"))
    For Each cel In rng
        If cel < 0 Then
            cel.Font.Color = 0
        Else
            cel.Font.Color = vbRed
        End If
    Next
End Sub

Open in new window

0
 
pauledwardianAuthor Commented:
Thanks Chris.
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!

  • 5
  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now