VBA Color cell range based on other cell value

[JAZ91]

Hi

For certain reasons I want to make this in VBA and not conditional formatting in excel.

I have a table of data where I want to color certain cells (a range) based on a value.
Fx
I want to color cell E4, F4, G4, H4, I4 and J4 based on the value in K4.
The value in K4 can be would be a due date so coloring could be something like gradual from green to red depending on how many days are left, or perhaps 1days left, 3days left, 7days,14 days left and +14days left.

And in the same row I also want to color in possibly a different color the
cell N4, O4 and P4 based on the value in P4.
The value in P4 can be open, closed, done, on hold.

I have searched but not found something where I can specify a specific range to color but only singe cells.
And since im new to VBA I have not managed to create something myself.

Hope someone can help

 

[6StringJazzer]

I have searched but not found something where I can specify a specific range to color but only singe cells.

If that is the crux of your problem then post code to color a single cell and we'll show you how to modify it for any range.

The "gradual from green to red" is the hardest part of this. Yes, certainly possible, but it would take me a while to write the code for your whole description and I'm sorry that don't have the time to do it all.

 

[JAZ91]

If that is the crux of your problem then post code to color a single cell and we'll show you how to modify it for any range.

The "gradual from green to red" is the hardest part of this. Yes, certainly possible, but it would take me a while to write the code for your whole description and I'm sorry that don't have the time to do it all.

What I got so far is this code. However it was working for one column and coloring the cells but it stopped working. And I also cannot seem to assign a macro button for some reason since it don't show up in the macro list. I originally wanted this to be automatic but since it wasn't working I tried to make a button to see if that helped, which was when I discovered I was not able to do even that. (ps. It may be something don't make sense in the code since I have copied something i searched up and tried to adapt it to my sheet.

Kind regards

JAZ

Sub Check_status(ByVal Target As Range)

Application.ScreenUpdating = False

Dim rg As Range
Dim cond1 As FormatCondition, cond2 As FormatCondition, cond3 As FormatCondition, cond4 As FormatCondition
Set rg = Range("A4:P200", Range("A4:P200").End(xlDown))

'clear any existing conditional formatting
rg.FormatConditions.Delete

'define the rule for each conditional format
Set cond1 = rg.FormatConditions.Add(xlCellValue, xlEqual, "Closed")
Set cond2 = rg.FormatConditions.Add(xlCellValue, xlEqual, "Open")
Set cond3 = rg.FormatConditions.Add(xlCellValue, xlEqual, "On hold")
Set cond4 = rg.FormatConditions.Add(xlCellValue, xlEqual, "Done")

'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



Application.ScreenUpdating = True

End Sub