How do I automatically highlight a cell?

[bmiranda]

I need to cross check data. I would like to do it visually.

I have a list of employees and a row of months

I have a "1" in any month where the employee received earnings.

I would like to cross check it with an hourly report.

I have a list of employee numbers, followed by months, and hours worked.

If they worked in a given month, I would like to highlight that cell..lets say green.

So if a cell is green and has a "1" in it, it checks.
Any cell that is green without a "1" needs to be examined further.

I'll attach an example

Thanks guys/gals


Example 2.xlsx

[Winon]

Hi bmiranda,

Have you tried Conditional Formatting?

[bmiranda]

I'm trying that, but I can't seem to get it right

[Winon]

One moment, let me have a look.

[FDibbins]

a quick tip for future reference...
excel has no way of knowing that when you type in 1, you mean January. likewise, it has no idea that when you type Jan, you mean January. dates/months/time are very special values in excel and need to calculated in a special way.

So, the way you have those tables set up, excel has no way to see that a value "1" is, to you, the same as a text "Jan".

If you can change your tables to make them more calc-friendly, that would be great, else we will have to work out a whole bunch of formulas to do the conversions for you. For instance, to chane the val;ue "1" into the month "January", would take a formula like...
=MONTH(DATE(2013,B2,1))

[Winon]

Hi bmiranda,

Do you want it like this?

[Winon]

@ FDibbins,

Good point/s you have raised. Maybe the OP wants to dictate months as numbers only, who knows?

[bmiranda]

We do want months as numbers only, for other data involved.

The example re-posted is close. The third tab is the desired outcome. See how it differs from what was posted?

[FDibbins]

posting months as numbers (or text - Jan/Feb etc) is fine, as long as you dont want to do anything with them later, as if they were dates

[Winon]

Hi bmiranda,

Just a note, with Conditional Formatting, if a Cell is Empty, it turns green for furthter investigation, else if any cell within the specified range contains any value, it will show as "white", which does not require any further investigation.

Hope that helps!

As per the revised attached WorkBook.

Also see the "Please consider" note at the bottom of this post.