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

--
Message posted from http://www.ExcelForum.com

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 < wrote:
> 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 the
> mileage table to change format (background red).
>
> I can get the conditional format to work, if I input a constant or pick
> cells where the date matches.
>
> What I am having trouble doing is getting the format to change from the
> LIST (column) of dates so all I have to do is input my fuel date and it
> will change the format of the same date in my mileage table.
>
> Thank you in advance.
>
> Cliff Watson
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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

--
Message posted from http://www.ExcelForum.com

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

--
Message posted from http://www.ExcelForum.com

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


---
Message posted from http://www.ExcelForum.com/

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.

CWatsonJr < wrote:
> Okay - this is weird.
>
> When I tested the conditional format using the countif in a blank cell
> (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 and
> background applied to the table cell (I just want to change the
> background when the condition is met) and in the test cell there is no
> formatting at all.
>
> I changed the test cell to replicate the table cell (that holds the
> 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 look
> at it?
>
> Thanks!
>
> Cliff
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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

CWatsonJr < wrote:
> 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.


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html