Rusty_Shackleford_Null
New Member
- Joined
- Jul 10, 2017
- Messages
- 9
- Office Version
- 365
- Platform
- Windows
- 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
Thank you for any help.
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.