Microsoft Excel - Conditional Formatting - Asked By Dan on 18-Aug-11 12:34 PM

Here is what I am trying to do.  In column G2 is a number that I can change at anytime.  Lets say I make G2 = 15.  I need H2 to change to a color base on a percentage of G2.  So if I input the number 5 in H2, H2 will turn yellow.


Green = 0%

Yellow = 1% to 10%

Red = greater than 10%

Devil Scorpio replied to Dan on 18-Aug-11 01:41 PM
HI Dan,

Please follow the following procedure to achieve ur goal

1. Open Excel
2. Goto Tools -> Macros -> Visual Basic Editor, it open VB editor.
3. Double click Sheet1 in left pane to open code workspace.
4. Copy & paste this code in sheet1 worspace. I wrote this for sheet1

Private Sub worksheet_change(ByVal Target As Range)
Dim varG2 As Integer
Dim varH2 As Integer
Dim varPercentage As Integer
varH2 = Sheet1.Range("H2")
varG2 = Sheet1.Range("G2")
varPercentage = (varH2 / varG2) * 100
If varPercentage = 0 Then
    Sheet1.Range("H2").Interior.Color = vbGreen
ElseIf varPercentage > 0 And varPercentage < 11 Then
    Sheet1.Range("H2").Interior.Color = vbYellow
ElseIf varPercentage > 10 Then
    Sheet1.Range("H2").Interior.Color = vbRed
End If
End Sub


5. Save it.

Please find the example attached below
Conditional Formatting.zip
wally eye replied to Dan on 18-Aug-11 04:41 PM
I would probably stick with conditional formatting:



The middle condition reads:

=and(g2>0,g2<=.1)