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

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Joined
Jul 10, 2017
Messages
8
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
Does the Tech2 range start in row 2?
Yes, Row 2

1622827697599.png
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,135
Office Version
  1. 365
Platform
  1. Windows
In that case the code I suggested should work, as long as the Act_Type2 range is the same size & also starts in row 2.
 
Joined
Jul 10, 2017
Messages
8
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
In that case the code I suggested should work, as long as the Act_Type2 range is the same size & also starts in row 2.
The ranges are the same, each starts in Row 2 and end at Row 241.

Thank you again for your help.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,135
Office Version
  1. 365
Platform
  1. Windows
In that case I don't know why it's not working on row 2, but glad you have solved it & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,640
Messages
5,654,537
Members
418,138
Latest member
agnesegras

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