How to clean up this conditional formatting code

RockandGrohl

Well-known Member
Joined
Aug 1, 2018
Messages
596
Office Version
  1. 2010
Platform
  1. Windows
Hi guys, believe it or not, this is my first time applying conditional formatting via VBA.

It looks a bit whacky, but I have four columns equally spaced apart and I want to apply four separate colour scale conditional formats to them (red = low, green = high)

Using the macro recorder gave me a bit of a mess, and I've cleaned it up a bit but am wondering if it can be made better?

VBA Code:
' Conditional Formatting Rule
Cells.FormatConditions.Delete


Range("Q4").Activate

Dim cfrng As Range
Set cfrng = Range(Cells(4, ActiveCell.Column + 0), Cells(Lastrow + 1, ActiveCell.Column + 0))

x = 1
For x = 1 To 4
With cfrng
    .FormatConditions.AddColorScale ColorScaleType:=3
    .FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    .FormatConditions(1).ColorScaleCriteria(1).Type = xlConditionValueLowestValue
    With cfrng.FormatConditions(1).ColorScaleCriteria(1).FormatColor
        .Color = 7039480
        .TintAndShade = 0
    End With
    .FormatConditions(1).ColorScaleCriteria(2).Type = xlConditionValuePercentile
    .FormatConditions(1).ColorScaleCriteria(2).Value = 50
    With cfrng.FormatConditions(1).ColorScaleCriteria(2).FormatColor
        .Color = 8711167
        .TintAndShade = 0
    End With
    .FormatConditions(1).ColorScaleCriteria(3).Type = xlConditionValueHighestValue
    With cfrng.FormatConditions(1).ColorScaleCriteria(3).FormatColor
        .Color = 8109667
        .TintAndShade = 0
    End With

ActiveCell.Offset(0, 11).Activate
Next x

Thanks!
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
57,010
Office Version
  1. 365
Platform
  1. Windows
How about
VBA Code:
For x = 0 To 45 Step 11
   With cfrng
       .Offset(, x).FormatConditions.AddColorScale ColorScaleType:=3
   End With
Next x
 
Solution

RockandGrohl

Well-known Member
Joined
Aug 1, 2018
Messages
596
Office Version
  1. 2010
Platform
  1. Windows
How about
VBA Code:
For x = 0 To 45 Step 11
   With cfrng
       .Offset(, x).FormatConditions.AddColorScale ColorScaleType:=3
   End With
Next x
Lol. I'm gonna start calling you the compressor. You take the goobledegook I write and compress it down to literally five lines haha.

What's with this by the way?

VBA Code:
For x = 0 To 45 Step 11
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
57,010
Office Version
  1. 365
Platform
  1. Windows
It's to offset the CF range in 11 column steps. So it starts on col Q then offset 11 cols to AB, AM etc
 

Watch MrExcel Video

Forum statistics

Threads
1,130,400
Messages
5,641,927
Members
417,247
Latest member
Chitaah

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top