Format Rows Red Based on Dates Entered in Cells 2

[databasegirl]

I have an excel file with two date fields. Column A contains the Incident Date and Column H contains the Preliminary Date. Column A will always have a date entered and will not be deleted. What I want is for the entire row for that incident to turn red if there is not a date entered in Column H (Preliminary Date) within 21 days of the date entered in Column A (Incident Date). I am not very familiar with writing code in Excel VBA. I would want this to be available for the entire worksheet. Any assistance would be great. Thanks!

 

[bluedragon2]

Have a look at conditional formatting...

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[databasegirl]

Conditional formatting works for the cell I'm on in the current row, but I want it applied to the entire column in the worksheet. Any ideas on this?

 

[SkipVought]

copy the "current row" and then use the Format Painter to paste the FORMAT to the other rows.

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[dgillz]

I also cannot get conditional formatting to do this, I had to use VBA code.

Private Sub Worksheet_Calculate()
 Dim iRow As Integer
    iRow = 1
    Do While Cells(iRow, 1).Value <> ""
        If Cells(iRow, 2).Value = "" Then
            With Rows(iRow)
                .Select
                .Interior.ColorIndex = 3
            End With
        
        Else
            With Rows(iRow)
                .Select
                .Interior.ColorIndex = 0
            End With
        End If
        iRow = iRow + 1
   Loop
End Sub

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[SkipVought]

"I also cannot get conditional formatting to do this"

You did not persist. You do not need to run VBA to do this.

BTW, .Select does absolutely NOTHING except slow down your process.

    Do While Cells(iRow, 1).Value <> ""
        With Rows(iRow)
            If Cells(iRow, 2).Value = "" Then
                .Interior.ColorIndex = 3
            Else
                .Interior.ColorIndex = 0
            End If
        End With
        iRow = iRow + 1
   Loop

Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}

 

[dgillz]

Skip,

Isn't your response VBA code?

I can use conditional formatting to set the color of the cell, but I could not use it to set the color of the entire row. If this can be done without VBA I would like to understand it.

Thanks for the tip on select, by the way.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[SkipVought]

"Isn't your response VBA code?"

YOUR code tightened up, merely as an exersize in coding principles. My admonition changes not.

"I can use conditional formatting to set the color of the cell, but I could not use it to set the color of the entire row. "
[tt]
Select THE ENTIRE RANGE that you want to CF -- ALL columns, ALL rows.

Select the interior color that is DEFAULT, ie your NO COLOR format, ZERO.

Format > Conditional Formatting ...

Change the Condition 1 dropdown to Formula is

enter the following formula in the textbox...
=$B1<>""

Change the Pattern Tab to Interior RED.
[/tt]
VOLA!

BTW, you REALLY have only ONE condition. The other is DEFAULT -- NO condition.


Skip,

_{Just traded in my old subtlety...
for a brand NUANCE!}