Microsoft Excel - Cell changes color based on %

Asked By Dan on 18-Aug-11 12:38 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 based on a percentage of G2.  So if I input the number 5 in H2, H2 will turn yellow, because 5 in H2 is 5% of G2.  I am trying to do this using conditional formatting.  Is this possible?


Green = 0%

Yellow = 1% to 10%

Red = greater than 10%
Riley K replied to Dan on 18-Aug-11 12:59 PM
here is some code to change the background color of a cell to the 
Devil Scorpio replied to Dan on 18-Aug-11 01:40 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
Dan replied to Devil Scorpio on 18-Aug-11 03:13 PM
This works for the one cell, But how can I get it to work for K2:V2 but only if a number is inputted into a cell?  If you can help me do that, then I am done.

Thank you for the help thus far!
Devil Scorpio replied to Dan on 18-Aug-11 10:22 PM
Hi Dan,

Do u want it to work for all cells in range K2:V2? Could u plz explain wht exactly u want, so tht i can help u.
Jitendra Faye replied to Dan on 19-Aug-11 12:42 AM

Conditional formatting enables you to highlight cells with a certain color, depending on the cell’s value.

For example, highlighting positive values with green background color, and negative values with red.
(actually not only the color can be set, but every aspect of cell formatting can be applied this way - font style, size, bold, underline etc.)

Follow these links-

http://www.contextures.com/xlcondformat03.html
http://www.cpearson.com/excel/cformatting.htm
http://www.free-training-tutorial.com/conditional-formatting.html

Hope this will help you.

Dan replied to Devil Scorpio on 22-Aug-11 06:11 AM
Yes. I need it to work for all the cells K2:V2, but only if something is inputted into a cell.  Otherwise, I want the cell to do nothing.