VBA to cycle through selected rows and apply conditional formatting

[davey4444]

Hello, I have some conditional formatting to apply across numerous rows in my worksheet and I would like to speed up that process as much as possible by using VBA. Ideally I will select some cells and then the VBA would run through each row and apply the Red (High) - Green (low) cell colour formatting, dependant on the column values. My code when I recorded it is below but I don't know how to scale this if, for example, I have selected cells P18:S200 and then I want it to run through each row.

Sub Macro4()

    Range("P18:S18").Select
    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 = 8109667
        .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 = 7039480
        .TintAndShade = 0
    End With
End Sub

Thanks in advance.

 

[Joe4]

I believe if you just remove the first line:

    Range("P18:S18").Select

then it will be applied to whatever range you have selected when you run the code.

It sounds like that may be what you are after?

 

[davey4444]

Hi, unfortunately not. If I select P10:Q15 then the conditional formatting assesses the data in those 10 cells and assigns the coding accordingly, so the highest value of those 10 cells is the most red. What I'm after is the data in P10:P15 to be formatted using the high/low values and then move on to Q10:Q15 and do the same.


Thanks.

 

[Joe4]

I am not crystal clear if you are looking to highlight whole rows, or individual cells.
It might be better if you could post a small data sample along with your expected result
An example often goes a long way in clearly showing us exactly what you are after.

 

[Joe4]

I am looking for that clarification, because in you original post, it looks like you are doing this to individual cells across a single row (P18:S18).
But then in your second post, you are talking about individual cells in a single column (P10:P15).
So I am unclear exactly what you are trying to do.

 

[davey4444]

Hi, thanks for taking the time to reply. I've shown some examples below, the top table is what I'd like the formatting to look like. The top table shows what I'd like to happen where I'd select A1:C2 so A1:C1 is then formatted with the largest number (3) in red and then the smallest (1) as green and D1 remains unaffected as it's not selected. Then A2:C2 goes through the same formatting based on the cell values (8 and 6, largest to smallest).
In the second table is what happens when the above code is run with A1:C2 is selected. The largest value (8) is red but the 6 would be orange rather than green. Similarly, the 3 is not red despite it being the largest value in that row.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]Text[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]Text[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]Text[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]Text[/TD]
[/TR]
</tbody>[/TABLE]

 

[davey4444]

I am looking for that clarification, because in you original post, it looks like you are doing this to individual cells across a single row (P18:S18).
But then in your second post, you are talking about individual cells in a single column (P10:P15).
So I am unclear exactly what you are trying to do.

Apologies, my second post confused row and column coordinates. Hopefully my example gives clarity.

 

[Joe4]

Do you only care about the largest and smallest, or do you also want to see all the grandients in between (i.e. orange)?
If you only care about largest/smallest, I would probably use the formula option for Conditional Formatting, and make use of the MAX/MIN functions.

 

[davey4444]

Do you only care about the largest and smallest, or do you also want to see all the grandients in between (i.e. orange)?
If you only care about largest/smallest, I would probably use the formula option for Conditional Formatting, and make use of the MAX/MIN functions.

I'd like it to be all of the gradients in between. I can go through it manually however with so many rows it will take some time. It's not a pain if the selection range is hard-coded into the VBA if that helps, then the intial code and effectively select the first row, apply the formatting, then move on to the next row and apply etc..

 

[Joe4]

I rarely, if ever, use the gradient options on Conditional Formatting, so I would need to play around with it.
However, will the range ALWAYS be the same number of columns (i.e. P to S)?
If so, that may simplify things a bit.