Excel 2007 : Changing row background color based on AM or PM

[Lester_leaps_in]

I have a worksheet with 7 columns and 48 rows. The data in column A is a date and time displayed as "05/01/12 08:01 AM" (for example). I'd like to be able to have the background color in each row determined by whether the data in column A is AM or PM. I can make the cells in column A do this using conditional formatting, but I can't figure out how to make the entire row do it.

If someone can help, I will certainly appreciate it.

Please and thank you.

[dip11]

You can paste formatting along the row by dragging the row/column A across the row, then select Formatting Only

Alternatively, just add a check for all other cells in that given row to check vs $A$1 for 1st row, $A$2 for 2nd row etc

[Lester_leaps_in]

Thank you for your quick response. I tried your first suggestion, and either it's not working for me, or I'm doing it wrong. I should tell you that I'm somewhat of a novice.

I don't understand your second suggestion. I'm sorry.

Again, what I want is for all the cells in a row to look at the first cell, and if it contains "AM" as part of the entry in the cell, then color the background a certain color. If if information in the first cell contains "PM" then color the background a different color. So all the cells in a row will be color-coded based on whether the first cell in the row contains "AM" or "PM"

Thanks again.

[protonLeah]

1. Select the entire block of cells you wish to format.

2. Write the format rule as such:
=$x >=TIMEVALUE("12:00")
Where $x is the top left cell of the block (e.g. $A1)

[Lester_leaps_in]

Thank you for responding, and especially, for including an example. But, when I use your technique, I don't get what you got in your example. Instead, all of the rows change color.

I've attached some sample data to show you what I'm working with. I would like for AM rows to be red, and PM rows to be green.

If you are willing to take another crack at trying to help me, I would really appreciate it.

Thanks,
John

Copy of Blood Glucose Data.xlsx

[dip11]

Hi John,

Use this:
=TIMEVALUE($A5)>=0.5 (if PM, color)

To color AM, you can then use
=TIMEVALUE($A5)<0.5

All via conditional formatting

I've tested this versus your workbook and it worked

[Lester_leaps_in]

WOW!!! It does indeed work. Thank you very much. Thank you thank you thank you.

Did I say Thank You?

Thanks,
John

[dip11]

No worries.

Remember to mark the thread solved etc