Conditional formatting Coloring due dates

[JAZ91]

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.

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

 

[Beneindias]

Hi,

Is there any reason to doing it on vba?
You could do that with conditional formating...

 

[JAZ91]

Hi Beneindias,

Yes I would like in VBA because I have experienced that conditional formatting uses the excel feature at times can give trouble and get messed up when inserting and deleting row.
Also I would like to restrict end users from messing up the formatting, as it will be a standardized template for others as well.

And then I think it is a personal preference that I like when I have control over the document in one place, since I have some other macro's and coding in as well besides the above mentioned.

BR/

JAZ

 

[Beneindias]

Hi Beneindias,

Yes I would like in VBA because I have experienced that conditional formatting uses the excel feature at times can give trouble and get messed up when inserting and deleting row.
Also I would like to restrict end users from messing up the formatting, as it will be a standardized template for others as well.

And then I think it is a personal preference that I like when I have control over the document in one place, since I have some other macro's and coding in as well besides the above mentioned.

BR/

JAZ

Understood...

Then, I can't help you, sorry.

Maybe with a few ifs and elseifs....but nothing as elegant as what you already done.

 

[JAZ91]

Understood...

Then, I can't help you, sorry.

Maybe with a few ifs and elseifs....but nothing as elegant as what you already done.

That is fine hopefully someone else can.