Conditional formatting a range

[pruysie]

Hi,

I am having trouble with conditional formatting with VBA.
I have values in A1:A100 with percentages from -100% to 100%. I would like cells in B1:E100 (which have text) to change font color based on the value in column A. Eg B1:E1 font color based on A1, B83:E83 font color based on A83 etc.
I can obviously do this if I only want 4 different font colors but I want to use 7 colors. This is the color scheme I would like.

From -100% to -40% - Red
-40% to -25% - Orange
-25% to -15% - Pink
-15 to 5% - Black
5% to 15% - Lavender
15% to 25% - Blue
25% to 100% - Sea Green

Is there a way I could do this with VBA

 

[VoG]

See if this helps http://www.vbaexpress.com/kb/getarticle.php?kb_id=90

 

[pruysie]

In this example the conditional formatting for the cell is based on the value in that same cell. I am having trouble with formatting in one cell based on the value in a different cell.

 

[VoG]

Try this: right click the sheet tab, select View Code and paste in

Private Sub Worksheet_Change(ByVal Target As Range)
Dim icol As Integer
If Target.Column = 1 Then
    Select Case Target.Value
        Case -1 To -0.4: icol = 3
        Case -0.39 To -0.25: icol = 45
        Case -0.24 To -0.15: icol = 38
        Case -0.14 To 0.05: icol = 1
        Case 0.06 To 0.15: icol = 39
        Case 0.16 To 0.25: icol = 5
        Case 0.26 To 1: icol = 28
    End Select
    Target.Offset(, 1).Font.ColorIndex = icol
End If
End Sub

Then try changing the values in column A.