Color cell based on value gathered

[boninm]

HI

I'm using a VBA code to check if current date cell should be flagged based on a date to check against.
The code works fine, except cells don't want to change to color required.
Check output variable with msgbox

CHECK AGAINST DATE.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1					Today's date	2025-02-26	<<<= change cell color =>>>				2025-02-26
2
3					Date to check against:	2026-01-01					2025-03-31
4
5					if less than 365 but greather than 90 - Cell = Yellow
6
7					If less or equal to 90 - cell = Red
8
9
10	Using VBA code to check if current date should be flagged based on a date to check against
11	Code works fine except cells don't want to change to color required.
12
13
14
Sheet1

Cell Formulas
Range	Formula
F1,K1	F1	=NOW()

The VBA code under ThisWorkbook

'Private Sub Workbook_Open()
Sub CheckDate()

Dim VToday1
Dim VCellValue1
Dim VToday2
Dim VCellValue2
Dim VTimeLeft1 As Integer
Dim VTimeLeft2 As Integer

' VToday1 , VCellValue1 , VToday2 , VCellValue2 , VTimeLeft1 , VTimeLeft2

VToday1 = DateValue(Left(Cells(1, "F").Value, 10))
'MsgBox VToday1
VCellValue1 = Cells(3, "F").Value
'MsgBox VCellValue1
VTimeLeft1 = VCellValue1 - VToday1
'MsgBox VToday1, VCellValue1, VTimeLeft1
Cells(2, "F").Value = VTimeLeft1
'MsgBox VTimeLeft1
If VTimeLeft1 > 365 And VTimeLeft1 < 90 Then
Range("F1").Interior.ColorIndex = 6 ' yellow
End If

If VTimeLeft1 <= 90 Then
Range("F1").Interior.ColorIndex = 3 ' red
End If
'MsgBox VTimeLeft1




VToday2 = DateValue(Left(Cells(1, "K").Value, 10))
'MsgBox VToday2
VCellValue2 = Cells(3, "K").Value
'MsgBox VCellValue2
VTimeLeft2 = VCellValue2 - VToday2
'MsgBox VToday2, VCellValue2, VTimeLeft2
Cells(2, "K").Value = VTimeLeft2
'MsgBox VTimeLeft2
If VTimeLeft2 > 365 And VTimeLeft2 < 90 Then
Range("K1").Interior.ColorIndex = 6 ' yellow
End If

If VTimeLeft2 <= 90 Then
Range("K1").Interior.ColorIndex = 3 ' red
End If
'MsgBox VTimeLeft2

Application.Wait (Now + TimeValue("00:00:05"))
End Sub

------------------------------

And thank you in advance for helping.

Maurice

 

[Joe4]

Why are you using VBA to do this, when it appears that it can probably be done pretty easily using Conditional Formatting?
No sense in re-creating built-in functionality that works dynamically with no code, if you do not have to.

 

[boninm]

Why are you using VBA to do this, when it appears that it can probably be done pretty easily using Conditional Formatting?
No sense in re-creating built-in functionality that works dynamically with no code, if you do not have to.

I want to run this when i open the spreadsheet

if conditional formatting, how would you do this?
I've used conditional formatting before, but not to the extent that i want to do here.

Thanks for helping.

 

[Joe4]

Conditional Formatting is real-time, and runs all the time.
So as soon as you make any data changes, it automatically updates, as well as when the file is opened.

 

[boninm]

will try it

thank you

 

[boninm]

Works. Over complicated things.

Thanks for your help

Maurice

 

[Joe4]

OK, just note that you will probably need to use the formula option:

If you are trying to apply it to a multi-cell range, just select the range you want to apply it to before writing this formula, and then write the formula as it applies to the very FIRST cell in your selected range. Excel will adjust the formula it for all the other cells in the selected range. Just be sure to use "$" to anchor down any range that you don't want to change as the formula changes for other cells.

For example, let's say that you simply wanted to see if all the cells in B1:B10 are equal to the value in cell A1.
Then you would select the range B1:B10 and use this Conditional Formatting formula:

=B1=$A$1

Since you have multiple colors, you will need multiple Conditional Formatting rules (one for each color).
Post back here if you run into any trouble.

 

[Joe4]

Works. Over complicated things.

Thanks for your help

Maurice

I see you posted another reply as I was typing up my previous post.

Glad to hear you got it working!