Little help with conditional formatting 1

[WyldeCarde]

Hi all

What I am trying to do is, if a date in a subsection of column is less than the date stored in Cell D2, fill the entire row in light grey. If not, fill it in white.

So far I have (a lot if it from this very forum)-

Private Sub Worksheet_Activate()
Dim C As Range

Application.ScreenUpdating = False
For Each C In Range("e16:e255")
If C.Value < "D2" Then
C.EntireRow.Select
With Selection.Interior
.ColorIndex = 15
End With
Else
C.EntireRow.Select
With Selection.Interior
.ColorIndex = 16
End With
End If

Next C
End Sub

Which just colours everything in grey regardless of what values are in there. I get the feeling I'm doing something unspeakably obvious and stupid but my brain has gone all melty and I can't figure it out.

**********************************************************
If I had my own icecream van the chimes would play "Don't fear the reaper"
**********************************************************

 

[PHV]

I think you have to replace this:
If C.Value < "D2" Then
with this:
If C.Value < Range("D2") Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[RivetHed]

If C.Value < "D2" Then

That line's your problem, "D2" is text and not referring to a range.

 

[combo]

Instead of "D2" (just string, you compare with) use either [D2] or Range("D2").

combo

 

[WyldeCarde]

Oh god it's always something embarrassing whenever I post on here, thanks for the help, sorry for the stupidity

**********************************************************
If I had my own icecream van the chimes would play "Don't fear the reaper"
**********************************************************

 

[xlbo]

You know that unless you have >5 conditions, this can be done easily with conditional formatting on the spreadsheet - no code needed...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[WyldeCarde]

You know that unless you have >5 conditions, this can be done easily with conditional formatting on the spreadsheet - no code needed...

Rgds, Geoff

I know you can format cells with conditional formatting but how would I format the entire row the cell was on?

**********************************************************
If I had my own icecream van the chimes would play "Don't fear the reaper"
**********************************************************

 

[SkipVought]

"...but how would I format the entire row the cell was on? "

It's done regularly.

Select ALL the rows AND COLUMNS you want formatted.

Use ABSOLUTE & RELATIVE notations in your reference. For instance if you are testing a value in H2, then
[tt]
=$H2
[/tt]
makes column H absoute while the rows are relative.

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[WyldeCarde]

but not all the cells in the row will equal the cell and only the cells that do will get the formatting applied

**********************************************************
If I had my own icecream van the chimes would play "Don't fear the reaper"
**********************************************************

 

[SkipVought]

"...only the cells that do will get the formatting ..."

Then use ALL relative references.

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]

 

[WyldeCarde]

you've totally lost me, I have a list of data here's an example.

ATA Reg MSN Date Detail
33-00 T-Bird MSN1 16/04/2008 Detail1
33-00 T-Bird MSN2 11/02/2008 Detail2

So I would like to format the data from after 1st of April. With conditional formatting I can get the cell containing the date to change colour, but not the rest of the row, I have no idea what you think I would be comparing it to. Unless you think I have an enormous grid of numbers or dates for some reason?


**********************************************************
If I had my own icecream van the chimes would play "Don't fear the reaper"
**********************************************************

 

[xlbo]

Nope. Maybe this will shed some light:

I am asusming for this purpose that your data starts in column A and therefore the date to be compared is in column D. I am also assuming that the 1st row of data is row 2

So, select ALL the cells you want to apply the conditional formatting to. Your top left cell should be B2. This is your anchor point

Go Format>Conditional Formatting

Change "Value Is" dropdown to "Formula Is"

Enter

=$D2>datevalue("01/04/2008")

Set some formatting

Sit back and relax

This works because of the $

The $ makes the column reference ABSOLUTE so that ALL cells with this coonditional formatting applied to them check that column against the criteria. the rows are left without a $ as they need to be RELATIVE such that each row checks itself rather than an anchored row

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question