Looking to format complete row (all used cells) GREEN,
if date in column B is older than today()
I have the Workbook.Open event, but am lost with the module code.
It should loop thru the code every time the workbook is opened.
I like to do it this way so no matter how long the ws gets, it will "work".
So I guess we would need to find the end of each row, as well as the
end of the columns.

Hi,
This code isn't elegant, but it seems to work.

Sub Turn_Green()
Dim CountColumns As Double
Dim CountRows As Double
Dim Rng As Range
Dim Iloop As Double
CountColumns = Range("IV1").End(xlToLeft).Column
CountRows = Range("A65536").End(xlUp).Row
For Iloop = 1 To CountRows
If Cells(Iloop, "B") Date Then
Set Rng = Range("A" & Iloop)
Rng.Resize(1, CountColumns).Interior.ColorIndex = 4
End If
Next Iloop
End Sub

--
Ken Hudson


"BEEJAY" wrote:

Looking to format complete row (all used cells) GREEN,
if date in column B is older than today()
I have the Workbook.Open event, but am lost with the module code.
It should loop thru the code every time the workbook is opened.
I like to do it this way so no matter how long the ws gets, it will "work".
So I guess we would need to find the end of each row, as well as the
end of the columns.

Your Correct - It isn't Elegant.
But, Once I turned the greater than to less than,
it works exactly as I need it.
Thanks much for the prompt response.

"Ken Hudson" wrote:

Hi,
This code isn't elegant, but it seems to work.

Sub Turn_Green()
Dim CountColumns As Double
Dim CountRows As Double
Dim Rng As Range
Dim Iloop As Double
CountColumns = Range("IV1").End(xlToLeft).Column
CountRows = Range("A65536").End(xlUp).Row
For Iloop = 1 To CountRows
If Cells(Iloop, "B") Date Then
Set Rng = Range("A" & Iloop)
Rng.Resize(1, CountColumns).Interior.ColorIndex = 4
End If
Next Iloop
End Sub

--
Ken Hudson


"BEEJAY" wrote:

Looking to format complete row (all used cells) GREEN,
if date in column B is older than today()
I have the Workbook.Open event, but am lost with the module code.
It should loop thru the code every time the workbook is opened.
I like to do it this way so no matter how long the ws gets, it will "work".
So I guess we would need to find the end of each row, as well as the
end of the columns.

BTW, you wouldn't happen to be the Ken Hudson, formerly of GROVE
in the Royal City next door to the big family with all the blondies?
Wouldn't that be a strange coincidence.

"Ken Hudson" wrote:

Hi,
This code isn't elegant, but it seems to work.

Sub Turn_Green()
Dim CountColumns As Double
Dim CountRows As Double
Dim Rng As Range
Dim Iloop As Double
CountColumns = Range("IV1").End(xlToLeft).Column
CountRows = Range("A65536").End(xlUp).Row
For Iloop = 1 To CountRows
If Cells(Iloop, "B") Date Then
Set Rng = Range("A" & Iloop)
Rng.Resize(1, CountColumns).Interior.ColorIndex = 4
End If
Next Iloop
End Sub

--
Ken Hudson


"BEEJAY" wrote:

Looking to format complete row (all used cells) GREEN,
if date in column B is older than today()
I have the Workbook.Open event, but am lost with the module code.
It should loop thru the code every time the workbook is opened.
I like to do it this way so no matter how long the ws gets, it will "work".
So I guess we would need to find the end of each row, as well as the
end of the columns.