Conditional formating per row

[excel_2009]

Hi excel gurus!

I was wondering if someone could help me, I've attempted to create the following vba code to apply conditional formatting to a range of cells, whenever I attempt to execute the code nothing happens, I'm sure it's a simple fix but I'm not too sure what the issue is?

Sub test()

        Set Rng = Range("B2:G10" & Range("B" & Rows.Count).End(xlUp).Row)
        For Each c In Rng
            If c.Value <> "" Then
                r = c.Row


                    Selection.FormatConditions.AddColorScale ColorScaleType:=3
                    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
                    Selection.FormatConditions(1).ColorScaleCriteria(1).Type = _
                        xlConditionValueLowestValue
                    With Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor
                        .Color = 7039480
                        .TintAndShade = 0
                    End With
                    Selection.FormatConditions(1).ColorScaleCriteria(2).Type = _
                        xlConditionValuePercentile
                    Selection.FormatConditions(1).ColorScaleCriteria(2).Value = 50
                    With Selection.FormatConditions(1).ColorScaleCriteria(2).FormatColor
                        .Color = 8711167
                        .TintAndShade = 0
                    End With
                    Selection.FormatConditions(1).ColorScaleCriteria(3).Type = _
                        xlConditionValueHighestValue
                    With Selection.FormatConditions(1).ColorScaleCriteria(3).FormatColor
                        .Color = 8109667
                        .TintAndShade = 0
                        
                    End With
            End If
        Next


End Sub

Please can someone help me ?

 

[Joe4]

As long as you specify which columns to include, I think it should work like this:

Sub test()

        Dim [COLOR=#ff0000]myRng As Range[/COLOR], rng1 As Range, rng2 As Range
        Dim cell As Range
        Dim r As Long
        
[COLOR=#ff0000]'       Specify which columns to apply against
        Set myRng = Range("B:B, E:E, H:H, K:K, N:N, Q:Q, T:T, W:W, Z:Z")[/COLOR]
        
        Set rng1 = Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row)
        For Each cell In rng1
            r = cell.Row
            [COLOR=#ff0000]Set rng2 = Intersect(myRng, Rows(r))[/COLOR]
                              
                rng2.FormatConditions.AddColorScale ColorScaleType:=3
                rng2.FormatConditions(rng2.FormatConditions.Count).SetFirstPriority
                rng2.FormatConditions(1).ColorScaleCriteria(1).Type = _
                    xlConditionValueLowestValue
                With rng2.FormatConditions(1).ColorScaleCriteria(1).FormatColor
                    .Color = 7039480
                    .TintAndShade = 0
                End With
                rng2.FormatConditions(1).ColorScaleCriteria(2).Type = _
                    xlConditionValuePercentile
                rng2.FormatConditions(1).ColorScaleCriteria(2).Value = 50
                With rng2.FormatConditions(1).ColorScaleCriteria(2).FormatColor
                    .Color = 8711167
                    .TintAndShade = 0
                End With
                rng2.FormatConditions(1).ColorScaleCriteria(3).Type = _
                    xlConditionValueHighestValue
                With rng2.FormatConditions(1).ColorScaleCriteria(3).FormatColor
                    .Color = 8109667
                    .TintAndShade = 0
                End With
        Next cell

End Sub

 

[excel_2009]

Hi Joe

That's excellent!

Thank you so much!!!!