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]

A few issues.

You have built your range incorrectly:

Set Rng = Range("B2:G[COLOR=#ff0000]10[/COLOR]" & Range("B" & Rows.Count).End(xlUp).Row)

You should move that 10 in red, since you are calculating the row number, i.e.

Set Rng = Range("B2:G" & Range("B" & Rows.Count).End(xlUp).Row)

Secondly, if you are to look through all the cells in your range, you are actually not doing that.
You are using "Selection" over and over in your code, but this never changes, because as you go through your loops, you are actually never selecting anything.
Replace all references to "Selection" with your cell range, which is "c", i.e.

c.FormatConditions.AddColorScale ColorScaleType:=3

Also, why are you checking for the value of the cell before determining whether or not to apply Conditional Formatting to it? It seems like that could just simply be built into the conditions, and you could avoid the loops altogether and just apply the Conditional Formatting to the entire range (which would make your VBA code much more efficient and faster).

I am also unclear what you are calculating "r" in your loop when you do not appear to be doing anything with it.

 

[excel_2009]

Hi Joe,

Thank you for helping me with this, I've amended the code to the following:

Sub test()

        Set Rng = Range("B2:G" & Range("B" & Rows.Count).End(xlUp).Row)
        For Each c In Rng
            If c.Value <> "" Then
                              
                    c.FormatConditions.AddColorScale ColorScaleType:=3
                    c.FormatConditions(c.FormatConditions.Count).SetFirstPriority
                    c.FormatConditions(1).ColorScaleCriteria(1).Type = _
                        xlConditionValueLowestValue
                    With c.FormatConditions(1).ColorScaleCriteria(1).FormatColor
                        .Color = 7039480
                        .TintAndShade = 0
                    End With
                    c.FormatConditions(1).ColorScaleCriteria(2).Type = _
                        xlConditionValuePercentile
                    c.FormatConditions(1).ColorScaleCriteria(2).Value = 50
                    With c.FormatConditions(1).ColorScaleCriteria(2).FormatColor
                        .Color = 8711167
                        .TintAndShade = 0
                    End With
                    c.FormatConditions(1).ColorScaleCriteria(3).Type = _
                        xlConditionValueHighestValue
                    With c.FormatConditions(1).ColorScaleCriteria(3).FormatColor
                        .Color = 8109667
                        .TintAndShade = 0
                        
                    End With
            End If
        Next


End Sub

The goal of this is to have the formatting applied per row, when I run the code all of the cells which have a value populated are in green, the scale should be from red to green, green being the highest and red being the lowest (per row), is this possible?

It's my first attempt at trying to create a function, so I apologise for the mistakes

 

[excel_2009]

This is how it currently looks like:

This is how it should look:

 

[Joe4]

Can you post an example of what you data looks like, and what you want the results to look like?

You cannot upload files to this site. But there are tools you can use to post screen images. They are listed in Section B of this link here: http://www.mrexcel.com/forum/board-a...forum-use.html.
Also, there is a Test Here forum on this board that you can use to test out these tools to make sure they are working correctly before using them in your question.

 

[excel_2009]

Hi Joe, I think we both replied at the same time, are the screenshots sufficient?

 

[Joe4]

Do you see any in your post?
I do not, but I believe my company's security policy blocks certain types of image postings.

Either way, I do not see anything. My company's security policy also blocks file and image sharing sites.

 

[excel_2009]

SKU	X	Y	Z	A	B	C
ABS1	2	3		88	500
ABS2	45	8	1	3	123
ABS3	789	23			2
ABS4	32	3				22
ABS5	44	33		2t	23

<tbody>
</tbody>

Example data above, so what I'm attempting to do is use conditional formatting across the rows, so for the SKU ABS1 number 2 in column X would be in red, 3 would be in Red, 88 would be yellow, 500 would be in Green, for ABS2 45 would be lime, 8 would be yellow, 1 would be red, 3 would be light red and 123 would be in green.

I'm not able to use the xlam files unfortunately and the youtube links aren't working in regards to attaching images

 

[Joe4]

Try this:

Sub test()

        Dim rng1 As Range, rng2 As Range
        Dim cell As Range
        Dim r As Long
        
        Set rng1 = Range("B2:B" & Range("B" & Rows.Count).End(xlUp).Row)
        For Each cell In rng1
            r = cell.Row
            Set rng2 = Range(Cells(r, "B"), Cells(r, "G"))
                              
                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! it works great, would it be at all possible to tweak the code so that range is anything in column B, E, H,K,N,Q,T,W,Z? i've had to add in hidden columns between the missing columns specified but would like the conditional format to work across the row if the value resides within the columns B,E etc

Is this possible?