VBA Conditional Formatting issue with Dynamic Columns

[Rusty_Shackleford_Null]

Long time listener, first time caller here.

I need to apply conditional formatting to a cell based from conditions in two other cells. These cells are all contained in the same row, and the columns all have a named range. The rows are also dynamic.

The issue I have is the end users can configure the order of the columns, as well as remove them. I haven’t been able to modify the formula to work with those dynamic columns.

My example code below functions but is hard coded to Column A and O

Dim sht As Worksheet
Dim LC As Long
Dim LR As Long
Dim T1 As Long
Dim T2 As Long
Dim A1 As Long

Set sht = ActiveWorkbook.Worksheets("Report")

'Ctrl + Shift + Right (Range should be first cell in data set)
    LC = sht.Range("A1").CurrentRegion.Columns.Count
    LR = sht.Range("A1").CurrentRegion.Rows.Count
    T1 = Range("Tech_Team_Num2").Column
    T2 = Range("Tech2").Column
    A1 = Range("Act_Type2").Column

Debug.Print "LC (Last Column Number) = "; LC
Debug.Print "LR (Last Row Number) = "; LR
Debug.Print "T1 (Tech_Team_Num2) = "; T1
Debug.Print "T2 (Tech2) = "; T2
Debug.Print "A1 (Act_Type2) = "; A1

Application.StatusBar = "Determining Technician Name Complete"

Application.StatusBar = "Deleting Previous Technician Name Conditional Formatting"

Range("Tech2").FormatConditions.Delete

Application.StatusBar = "Deleting Previous Technician Name Conditional Formatting Complete"

    Cells(2, T2).Select

    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=AND($O2=""FL"",(COUNTIF($A2,""????????TR"")=1))"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent4
        .TintAndShade = -0.499984740745262
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Selection.Copy

    Cells(3, T2).Select
    Range(Cells(3, T2), Cells(LR, T2)).Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

Thank you for any help.

 

[Rusty_Shackleford_Null]

Hello,
Any thoughts on this one?

 

[Fluff]

A few questions
1) Is Tech2 column O and is it a single cell, or the used range?
2) Is Act_Type2 column A & is it a single cell or the used range?

 

[mikerickson]

I'm not sure what the problem is. Once the CF is set up, the formula will follow as cells as inserted, deleted or cut/pasted.
If the user's customization sticks to those processes, that won't mess up the existing CF.
Is the problem finding the correct columns during the CF set-up routine, depending on the user's customization.

 

[Rusty_Shackleford_Null]

A few questions
1) Is Tech2 column O and is it a single cell, or the used range?
2) Is Act_Type2 column A & is it a single cell or the used range?

They are both named ranges. Tech2 is located in Column O and Act_Type2 is in Column A. Those columns could be moved by the end user.

 

[Fluff]

Are they a single cell, the used range, or something else?

 

[Rusty_Shackleford_Null]

I'm not sure what the problem is. Once the CF is set up, the formula will follow as cells as inserted, deleted or cut/pasted.
If the user's customization sticks to those processes, that won't mess up the existing CF.
Is the problem finding the correct columns during the CF set-up routine, depending on the user's customization.

Yes, the problem is finding the correct columns during the CF set up Routine. I was unsuccessful in setting up the CF formula with a variable.

 

[Rusty_Shackleford_Null]

Are they a single cell, the used range, or something else?

Used ranges, sorry for not clarifying that earlier.

 

[Fluff]

Ok, how about

Sub chk()
Dim sht As Worksheet
Dim LC As Long
Dim LR As Long
Dim T1 As Long
Dim T2 As Long
Dim A1 As Long

Set sht = ActiveWorkbook.Worksheets("Report")

'Ctrl + Shift + Right (Range should be first cell in data set)
    LC = sht.Range("A1").CurrentRegion.Columns.Count
    LR = sht.Range("A1").CurrentRegion.Rows.Count
    T1 = Range("Tech_Team_Num2").Column
    T2 = Range("Tech2").Column
    A1 = Range("Act_Type2").Column

Debug.Print "LC (Last Column Number) = "; LC
Debug.Print "LR (Last Row Number) = "; LR
Debug.Print "T1 (Tech_Team_Num2) = "; T1
Debug.Print "T2 (Tech2) = "; T2
Debug.Print "A1 (Act_Type2) = "; A1

Application.StatusBar = "Determining Technician Name Complete"

Application.StatusBar = "Deleting Previous Technician Name Conditional Formatting"

Range("Tech2").FormatConditions.Delete

Application.StatusBar = "Deleting Previous Technician Name Conditional Formatting Complete"

    With Range("Tech2")

    .FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=AND(rc=""FL"",(COUNTIF(rc[" & A1 - T2 & "],""????????TR"")=1))"
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    With .FormatConditions(1).Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent4
        .TintAndShade = -0.499984740745262
    End With
    .FormatConditions(1).StopIfTrue = False
   End With
End Sub

 

[Rusty_Shackleford_Null]

It works great , with a few changes.

If i used the With Range("Tech2") at the start of the CF Formula it wouldn't apply the CF Formula to row 2.
If I use a cell statement it works fine.

The CF formula were swapped, but work fine after I made the changes in the code attached below.

I tried to use the RC format in the CF formula, before posting my question to the forum.
I'm sure I had a syntax error in my formula, and I still do not understand the RC syntax that was used to resolve this.
If you have a recommendation to a reference document for that information, that would be awesome.

Thank you so much for helping me resolve this!?

Dim sht As Worksheet
Dim LC As Long
Dim LR As Long
Dim T1 As Long
Dim T2 As Long
Dim A1 As Long

Set sht = ActiveWorkbook.Worksheets("Report")

'Ctrl + Shift + Right (Range should be first cell in data set)
    LC = sht.Range("A1").CurrentRegion.Columns.Count
    LR = sht.Range("A1").CurrentRegion.Rows.Count
    T1 = Range("Tech_Team_Num2").Column
    T2 = Range("Tech2").Column
    A1 = Range("Act_Type2").Column

Debug.Print "LC (Last Column Number) = "; LC
Debug.Print "LR (Last Row Number) = "; LR
Debug.Print "T1 (Tech_Team_Num2) = "; T1
Debug.Print "T2 (Tech2) = "; T2
Debug.Print "A1 (Act_Type2) = "; A1

Application.StatusBar = "Determining Technician Name Complete"

Application.StatusBar = "Deleting Previous Technician Name Conditional Formatting"

Range("Tech2").FormatConditions.Delete

Application.StatusBar = "Deleting Previous Technician Name Conditional Formatting Complete"

'    With Range("Tech2")
    Cells(2, T2).Select

'    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
'        "=AND($O2=""FL"",(COUNTIF($A2,""????????TR"")=1))"
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=AND(rc[" & A1 - T2 & "]=""FL"",(COUNTIF(rc[" & T1 - T2 & "],""????????TR"")=1))"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Font
        .ThemeColor = xlThemeColorDark1
        .TintAndShade = 0
    End With
    With Selection.FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent4
        .TintAndShade = -0.499984740745262
    End With
    Selection.FormatConditions(1).StopIfTrue = False
'    End With