VBA Conditional Formatting issue with Dynamic Columns

Joined
Jul 10, 2017
Messages
8
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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

VBA Code:
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.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,772
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,078
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.
 
Joined
Jul 10, 2017
Messages
8
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,772
Office Version
  1. 365
Platform
  1. Windows
Are they a single cell, the used range, or something else?
 
Joined
Jul 10, 2017
Messages
8
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

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.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,772
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
VBA Code:
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
 
Solution
Joined
Jul 10, 2017
Messages
8
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
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!😀

VBA Code:
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
 

Forum statistics

Threads
1,143,909
Messages
5,721,455
Members
422,363
Latest member
Bogus_Potatoes

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