VBA - Conditional Formatting with variable reference cell?

PaigeDelotta

New Member
Joined
Mar 24, 2014
Messages
1
Hello, I've searched everywhere and cannot find a solution to my situation. Many thanks in advance for any guidance.

I have a macro that conditionally formats a column of variable row length to certain conditions based on the values found in an adjacent column, for which I used the following code:

Dim myR As Long
Range("C10:C" & myR).Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=D10>0.05"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.349986266670736
End With
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.349986266670736
End With
Selection.FormatConditions(1).StopIfTrue = False

However, now I have a situation where not only do I have a variable amount of rows, but I also a variable amount of columns needing formatting.

For example I'd need to format columns C:H based on scores in columns I:N. I'm not having an issue selecting the variable rows and variable columns of what will be formatted, but I can't figure out how to reference a variable starting place for this part of the code as found above: "Formula1:="=D10>0.05"

Any ideas on this? I'm a newbie and self taught, mainly through forums like these. I would appreciate any and all assistance! Thanks :)
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Forum statistics

Threads
1,181,182
Messages
5,928,581
Members
436,608
Latest member
nevers0220

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