Conditional Formatting Help

[swmwshrk]

Ii have a sreadsheet created by someone else to track my sons cub scout badges. There is a summary page that indicates that an award was earned. Once the award is marked as earned, The cell automatically fills with color, indicating the award needs to be presented. Then once the award is presented, the user enters the date it was presented, and the highlight goes away so the cell is white again.

I looked for a formula and for conditional formatting and didn't see anything under those. How can I do this in a workbook I am creating. In figure 2 below row 2 middle cell would have a background color, In figure 1 & 3 the cell would be white (or whatever default color was used for the page.

Figure 1

Award Name	Complete/Partial	Date
Arrow of Light	P

Figure 2

Award Name	Complete/Partial	Date
Arrow of Light	C

Figure 3

Award Name	Complete/Partial	Date
Arrow of Light	C	7/8/12

[BenMiller]

Assuming it's cell B2, use this as your conditional formatting formula. =(B2="C")*(C2<>0)

[swmwshrk]

Unless I'm setting something up wrong, that formula gives me the opposite of what I want. Here are my steps

Select cell C18 only (cell is not merged) Format>>Conditional Formatting>>Formula Is>>=(C18="C")*(D18<>0)>>Format>>Font>>Color White>>Patterns>>Color Black>>OK>>Add>>OK

My result is that when I enter data into D18, C18 becomes white font on black background, when D18 is empty C18 is black font on white background.

I want this reversed. Am I doing something wrong?

[BenMiller]

I'm sorry, I misunderstood. That formula would be right for the DIFFERENT format. The "regular" format would be when there is no date, and the cell says P. Right? So try =(C18="P")*(D18=0)

[swmwshrk]

Thank you so much. I got it now. The formula I was looking for

Please Login or Register  to view this content.

.