Hi
So I have been searching the internet and done trial and error on this and unfortunately ended in error every time, so maybe i'm just dumb.
But, I would really appreciate if someone could help me work out this conditional formatting in VBA code format.
Most of the code works, however I have recently added cond5, cond6, cond7 and cond8 to my formatting.
I have a column range going from K4:K200 which contain a deadline date (dd-mm-yyyy).
I would like to have coloring of the background / interior of the cell in this column if;
- the date of the deadline or overdue
- 7 days before deadline date
- 30 days before deadline
And then i have another column P, which contains a status for each row. I would like if that staus is set as "Done" then the deadline date greyed out with a fainter/ lighter font as well.
Below i have inserted my code. Again the code have been working before my addition of rg2 and the cond5 to cond8.
BR/
JAZ
So I have been searching the internet and done trial and error on this and unfortunately ended in error every time, so maybe i'm just dumb.
But, I would really appreciate if someone could help me work out this conditional formatting in VBA code format.
Most of the code works, however I have recently added cond5, cond6, cond7 and cond8 to my formatting.
I have a column range going from K4:K200 which contain a deadline date (dd-mm-yyyy).
I would like to have coloring of the background / interior of the cell in this column if;
- the date of the deadline or overdue
- 7 days before deadline date
- 30 days before deadline
And then i have another column P, which contains a status for each row. I would like if that staus is set as "Done" then the deadline date greyed out with a fainter/ lighter font as well.
Below i have inserted my code. Again the code have been working before my addition of rg2 and the cond5 to cond8.
VBA Code:
Sub Check_status()
Application.ScreenUpdating = False
Dim rg1 As Range, rg2 As Range
Dim cond1 As FormatCondition, cond2 As FormatCondition, cond3 As FormatCondition, cond4 As FormatCondition, _
cond5 As FormatCondition, cond6 As FormatCondition, cond7 As FormatCondition, cond8 As FormatCondition
Set rg1 = Range("A4:P200", Range("A4:P200").End(xlDown))
Set rg2 = Range("K4:K200", Range("K4:K200").End(xlDown))
'clear any existing conditional formatting
rg.FormatConditions.Delete
'define the rule for each conditional format
Set cond1 = rg1.FormatConditions.Add(xlCellValue, xlEqual, "Closed")
Set cond2 = rg1.FormatConditions.Add(xlCellValue, xlEqual, "Open")
Set cond3 = rg1.FormatConditions.Add(xlCellValue, xlEqual, "On hold")
Set cond4 = rg1.FormatConditions.Add(xlCellValue, xlEqual, "Done")
Set cond5 = rg2.FormatConditions.Add(Type:=xlExpression, Formula1:="=Today(
Set cond6 = rg2.FormatConditions.Add(Type:=xlExpression, Formula1:="=
Set cond7 = rg2.FormatConditions.Add(Type:=xlExpression, Formula1:="=
Set cond8 = rg2.FormatConditions.Add(Type:=xlExpression, Formula1:="=
'define the format applied for each conditional format
With cond1
.Interior.Color = vbGreen
.Font.Color = vbBlack
End With
With cond2
.Interior.Color = vbRed
.Font.Color = vbWhite
End With
With cond3
.Interior.Color = vbYellow
.Font.Color = vbRed
End With
With cond4
.Interior.Color = vbBlue
.Font.Color = vbWhite
End With
With cond5
.Interior.Color = RGB(143, 220, 178) 'Green - (purple RGB 178, 162, 197)(Grey RGB 155, 155, 155)
.Font.Color = vbWhite
End With
With cond6
.Interior.Color = RGB(255, 142, 142) 'Red RGB(255, 142, 142)
.Font.Color = vbWhite
End With
With cond7
.Interior.Color = RGB(242, 204, 89) 'Orange RGB(242, 204, 89)
.Font.Color = vbWhite
End With
With cond8
.Interior.Color = RGB(87, 193, 231) 'Blue RGB(87, 193, 231)
.Font.Color = vbWhite
End With
Application.ScreenUpdating = True
End Sub
BR/
JAZ