Dynamic Conditional Formatting Macro

[Gonzalo De La Torre]

Good morning,

I am developing a macro to perform conditionall formatting with several conditions. My problem is that i cant get the program to perform the operations because I dont know how to address the row number where i want to perform the conditional formatting. Check the XXX which is where im having my problem. Here is the program:

Sub CF()
' CREATE CURRENT STATUS FORMULA
Range("F2:F18").FormulaR1C1 = "=RC[-3]-RC[-1]"
' CREATE RANGE FOR RowNdx
For RowNdx = Range("F2").End(xlDown).Row To 2 Step -1
 
' %CR IS GREATER THAN BUDGET
If Cells(RowNdx, "C").Value > Cells(RowNdx, "E").Value Then
Range("FXXX:crash:").Select
Selection.FormatConditions.AddDatabar
Selection.FormatConditions(Selection.FormatConditions.Count).ShowValue = True
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1)
.MinPoint.Modify newtype:=xlConditionValueNumber, newvalue:=0
.MaxPoint.Modify newtype:=xlConditionValueNumber, newvalue:=0.0372
End With
With Selection.FormatConditions(1).BarColor
.Color = 255
.TintAndShade = 0
End With
 
' THERE IS NO BUDGET
ElseIf Cells(RowNdx, "E").Value = 0 Then
Range("FXXX:crash:").Select
Selection.FormatConditions.AddDatabar
Selection.FormatConditions(Selection.FormatConditions.Count).ShowValue = True
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1)
.MinPoint.Modify newtype:=xlConditionValueNumber, newvalue:=0
.MaxPoint.Modify newtype:=xlConditionValueNumber, newvalue:="=$C$XXX:crash:"
End With
With Selection.FormatConditions(1).BarColor
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
End With
 
' %CR EQUAL OR LOWER THAN BUDGET BUT GREATER THAN TARGET
ElseIf Cells(RowNdx, "C").Value <= Cells(RowNdx, "E").Value And Cells(RowNdx, "C").Value > Cells(RowNdx, "D").Value Then
Range("FXXX").Select
Selection.FormatConditions.AddDatabar
Selection.FormatConditions(Selection.FormatConditions.Count).ShowValue = True
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1)
.MinPoint.Modify newtype:=xlConditionValueNumber, newvalue:=0
.MaxPoint.Modify newtype:=xlConditionValueNumber, newvalue:="=-$E$XXX"
End With
With Selection.FormatConditions(1).BarColor
.Color = 65280
.TintAndShade = 0
End With
 
' %CR IS EQUAL OR LOWER THAN TARGET
ElseIf Cells(RowNdx, "C").Value <= Cells(RowNdx, "D").Value Then
Range("FXXX").Select
Selection.FormatConditions.AddDatabar
Selection.FormatConditions(Selection.FormatConditions.Count).ShowValue = True
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1)
.MinPoint.Modify newtype:=xlConditionValueNumber, newvalue:=0
.MaxPoint.Modify newtype:=xlConditionValueNumber, newvalue:="=-$D$XXX"
End With
With Selection.FormatConditions(1).BarColor
.Color = 65535
.TintAndShade = 0
End With
 
End If
Next RowNdx
 
Range("F7,F9,F13,F15,F17").ClearContents
End Sub

 

[Smitty]

Welcome to the Board!

I don't see where you're setting "xxx"? Are you trying to use RowNdx? If so, you can do this:

Range("F" & RowNdx)...

And note there's no need to select. You can wrap in a With statement:

With Range(""F" & RowNdx")
  .FormatConditions.AddDatabar
  .FormatConditions(Selection.FormatConditions.Count).ShowValue = True
  .FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  ' ETC
End With

HTH,

 

[mancubus]

instead of

RowNdx = Range("F2").End(xlDown).Row

use

RowNdx = Range("F65536").End(xlUp).Row

if there are blank cells in column F, your Row variable will not cover the last row of your table.

 

[Gonzalo De La Torre]

What im trying to get istead of the XXX is the current rownumber I'm currently in. That rownumber is the same as the one that its being stored in RowNdx.

Also i want to know how to use it in

newvalue:="=$C$XXX"

 

[Smitty]

What im trying to get istead of the XXX is the current rownumber I'm currently in. That rownumber is the same as the one that its being stored in RowNdx.

Range("F" & RowNdx)...

Also i want to know how to use it in

newvalue:="=$C$XXX"

newvalue:= Range("C" & RowNdx).Value