Good afternoon,
I am working on a macro for tracking training dates and notifying the user when training needs to be scheduled. Currently I have my macro doing the following conditions:
-Determines the last row used in the worksheet to know the range to apply the formats to
-Apply the conditional formatting correctly to each column based on certain criteria's in that column's cells
I am trying to add a QOL (Quality of life) improvement that examines a baseline training course and determines if the refresher course needs to be taken based on a specific timeframe, but not having luck so far. For example, in Column M, we track when a person does Rigging training. However, that training is only good for 3 years. Prior to the 3 year expiration date, the employee needs to take a Rigging Refresher training course which is in Column N. I want to add a routine in my code that looks at the Column M Date and if the user inputted R in the cell for Column N, it changes the cell in Column N to brown if today's date is within 90 days of the date in Column M. I know how to do the conditional formatting but not sure on the If Then Routine to look at each row in Column M and apply the conditional formatting in Column N.
Here is part of the code from my macro.
Thanks for any assistance
I am working on a macro for tracking training dates and notifying the user when training needs to be scheduled. Currently I have my macro doing the following conditions:
-Determines the last row used in the worksheet to know the range to apply the formats to
-Apply the conditional formatting correctly to each column based on certain criteria's in that column's cells
I am trying to add a QOL (Quality of life) improvement that examines a baseline training course and determines if the refresher course needs to be taken based on a specific timeframe, but not having luck so far. For example, in Column M, we track when a person does Rigging training. However, that training is only good for 3 years. Prior to the 3 year expiration date, the employee needs to take a Rigging Refresher training course which is in Column N. I want to add a routine in my code that looks at the Column M Date and if the user inputted R in the cell for Column N, it changes the cell in Column N to brown if today's date is within 90 days of the date in Column M. I know how to do the conditional formatting but not sure on the If Then Routine to look at each row in Column M and apply the conditional formatting in Column N.
Here is part of the code from my macro.
Code:
Sub TrainingConditionalFormatting()
Dim LastRow As Long
Dim IfRange As Range
'Find the last used row in a Column
With ActiveSheet
LastRow = .Cells(.Rows.Count, "I").End(xlUp).Row
End With
'Rigging Conditioning
Range(Cells(4, 13), Cells(LastRow, 13)).Select
Selection.FormatConditions.Add Type:=xlTextString, String:="R", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent1
.TintAndShade = -0.249946592608417
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlTextString, String:="NR", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.Pattern = xlNone
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlTextString, String:="AR", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.Pattern = xlNone
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=LEN(TRIM(M4))=0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlTextString, String:="S", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
'Rigging Refresher Conditioning
Set IfRange = Range(Cells(4, 13), Cells(LastRow, 13))
Range(Cells(4, 14), Cells(LastRow, 14)).Select
Selection.FormatConditions.Add Type:=xlTextString, String:="R", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent1
.TintAndShade = -0.249946592608417
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlTextString, String:="NR", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.Pattern = xlNone
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlTextString, String:="AR", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.Pattern = xlNone
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=LEN(TRIM(N4))=0"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlTextString, String:="S", _
TextOperator:=xlContains
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlBetween, _
Formula1:="=$I$1+90", Formula2:="=$I$1"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent2
.TintAndShade = -0.249946592608417
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="=$I$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
.Color = 255
.TintAndShade = 0
End With
Thanks for any assistance