VBA Conditional Formatting issue with Dynamic Columns

Joined
Jul 10, 2017
Messages
9
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

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Does the Tech2 range start in row 2?
Yes, Row 2

1622827697599.png
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,148
Members
448,552
Latest member
WORKINGWITHNOLEADER

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
Back
Top