Conditional Format on Date Match

[CWatsonJr]

I am keeping track of my vehicle mileage and fuel used.

I have a table of dates where I keep my mileage.
I have a table of dates to track my fuel.

All on the same sheet.

On the date that I get fuel, I want the (date matching) cell in th
mileage table to change format (background red).

I can get the conditional format to work, if I input a constant or pic
cells where the date matches.

What I am having trouble doing is getting the format to change from th
LIST (column) of dates so all I have to do is input my fuel date and i
will change the format of the same date in my mileage table.

Thank you in advance.

Cliff Watso

 

[Debra Dalgleish]

Select the cells that contain mileage dates
Choose Format>Conditional Formatting
From the first dropdown, choose Formula Is
In the formula box, type a formula that refers to the active cell
and to the range of cells that contain fuel dates.
For example, with fuel dates in cell F2:F20 --
=COUNTIF($F$2:$F$20,A2)
Click the Format button, and choose red on the Patterns tab
Click OK, click OK

 

[CWatsonJr]

Thank you very much!!!!

I was trying all kinds of functions without any positive results.

I really appreciate your help!

Using this function is so easy compared to all the functions an
formulas I was trying to mix/match and write

Clif

 

[CWatsonJr]

Okay - this is weird.

When I tested the conditional format using the countif in a blank cel
(using the list of fuel dates) it worked just fine.

When I changed the cell reference to the table that holds the dates..
it didn't work.

The difference is there is already a font, bold, and border an
background applied to the table cell (I just want to change th
background when the condition is met) and in the test cell there is n
formatting at all.

I changed the test cell to replicate the table cell (that holds th
date) and it worked fine in the test cell.

Why do you think it won't work in the real cell?

Should I upload my spreadsheet to my server so someone can take a loo
at it?

Thanks!

Clif

 

[CWatsonJr]

Okay - here is how I solved the problem...

I cleared all the date cells (format, contents, formulas, etc) and then
tested them and they worked fine.

I then applied the conditional format to the cells

Next I applied the formulas for the dates

I then applied the other formatting (background, font, borders, etc.).

Now it works fine. I have no idea why it didn't work before.

Thank you again for your help.

Cliff

 

[Debra Dalgleish]

What doesn't work? Does it colour the wrong dates, no dates, or all the
dates?

Is there really a date in the table cell (try to change its date format,
and see what happens)?

Does the table cell data validation refer to the correct range in the
fuel date cells?

It looks like you're posting through Excel Forum, and I think you can
post files there. If you can post a small sample file, someone may take
a look.

 

[Debra Dalgleish]

Great! Thanks for letting me know how you got it to work.