Color rows based on a cell value

[pbreslin]

Hello -
Windows XP
Excel 2003

I want to color a row based on a cell value.
Specifically, I have about 100 rows where I keep licensing data.
I have a column entitled 'DAYS TO EXPIRATION' and another 'DAYS TO
RENEWAL'
If the days to expiration or renewal are 60 or less, then I want the
whole row's font to change to red from 'automatic' (black).
I can change the color of the cell using conditional formatting.
The problem is some of the rows contain no expiration - the days to
expiration are blank because the license doesn't expire or must be
renewed.
So, I need either VBA or a condition formatting snippet to:
Check the column(s) for numbers, if there is a number, and if the
number is 60 or less (<60), then change the row's font color to red.
Thanks,
Paul

 

[Per Jessen]

Hi Paul

Conditional formatting will do the job.

with 'Days to expiration' in column A, select row 1 and goto conditional
formatting > Formula is:

=AND($A1<=60,$A1<>"")

Format as desired, and use the format painter to format the desired range.

Hopes this helps

 

[A possible solution]

I would suggest using a macro like this:

Sub MarkExpiredLicenses()

'This subroutine assumes that you have a column in addition
'to your 'DAYS TO EXPIRATION' and 'DAYS TO RENEWAL' columns that
'whose cells are not empty for EACH of the licenses in your worksheet
'Let us assume that that column is A
'Let us also assume that column B is your 'DAYS TO EXPIRATION' column

Dim license As range
Set license = range("A1")
Dim iLicense As Integer
iLicense = 0
Do
If license.Offset(iLicense, 1).Value <= 60 Then
license.Offset(iLicense, 1).Font.ColorIndex = 3
license.Offset(iLicense, 1).Font.Bold = True
Else
license.Offset(iLicense, 1).Interior.ColorIndex = 0
license.Offset(iLicense, 1).Font.Bold = False
End If
iLicense = iLicense + 1
If license.Offset(iLicense, 0).Value = "" Then Exit Do
Loop
End Sub

Hope this helped.
Best wishes,
Rolf