Conditional Formatting (Excel 97)

Topic

New Reply

I am looking for help to create a conditional format formula.

The attached file shows what I am trying to do.

Kerry

Reply | Quote

Replies

Highlight Cells K5:O50 (extend the column number and rows as desired to expand the range)
Format -conditional formatting..
Select: “Formula is” in left combobox
Add to right box (No quotes)”
“=AND(MONTH($G5)=MONTH(K$2),NOT(ISBLANK($G5)),NOT(ISBLANK(K$2)))”

Patteren tab
Select gray shading

The formula looks for a match, but also makes sure that G is not blank (Month = 1 for “blank”) as well as nothing in ROw 2.

Steve

Reply | Quote

Thanks Steve. I gave this a go but with no luck. I have attached a the spreadsheet instead of a screenshot.

Kerry

Reply | Quote

You set it as condition 3. Conditons are NOT cumulative. SInce the 1st condition is met, that formatting takes place (no matter what the other conditions have).

If Condition 1 is false then condition 2 is examined If TRUE that formatting is active.
IF BOTH condition 1 NAD condition 2 is false then condition 3 is checked.

You can ONLY have 4 formats:
Format 1 (Std format via format cells when NO conditions are true)
Format 2 when CONDITION 1 is TRUE
Format 3 when CONDITION 1 is FALSE and Condition 2 is TRUE
Format 4 when CONDITION 1 is FALSE and Condition 2 is FALSE and condition3 is TRUE

If you want shaded for date AND Green based on col I you would have to AND the 2 conditions:
=AND($i4<k4,MONTH($G4)=MONTH(K$2),NOT(ISBLANK($G4)),NOT(ISBLANK(K$2)))

Then you would need another for shaded and ORANGE (when K is NOT larger than I)
=AND($i4<k4,MONTH($G4)=MONTH(K$2),NOT(ISBLANK($G4)),NOT(ISBLANK(K$2)))

Then for the third (green no shading)
=$i4<k4

For orange with no shading you would have to set the format of the cells directly (format -cells)

If you need MORE than 4 formats with 3 conditions, you will have to have a macro do it (which could slow things down considerably to keep it live and always up-to-date, since the macro would have to run on every worksheet change and reformat EVERYTHING.

Steve

Reply | Quote

You set it as condition 3. Conditons are NOT cumulative. SInce the 1st condition is met, that formatting takes place (no matter what the other conditions have).

If Condition 1 is false then condition 2 is examined If TRUE that formatting is active.
IF BOTH condition 1 NAD condition 2 is false then condition 3 is checked.

You can ONLY have 4 formats:
Format 1 (Std format via format cells when NO conditions are true)
Format 2 when CONDITION 1 is TRUE
Format 3 when CONDITION 1 is FALSE and Condition 2 is TRUE
Format 4 when CONDITION 1 is FALSE and Condition 2 is FALSE and condition3 is TRUE

If you want shaded for date AND Green based on col I you would have to AND the 2 conditions:
=AND($i4<k4,MONTH($G4)=MONTH(K$2),NOT(ISBLANK($G4)),NOT(ISBLANK(K$2)))

Then you would need another for shaded and ORANGE (when K is NOT larger than I)
=AND($i4<k4,MONTH($G4)=MONTH(K$2),NOT(ISBLANK($G4)),NOT(ISBLANK(K$2)))

Then for the third (green no shading)
=$i4<k4

For orange with no shading you would have to set the format of the cells directly (format -cells)

If you need MORE than 4 formats with 3 conditions, you will have to have a macro do it (which could slow things down considerably to keep it live and always up-to-date, since the macro would have to run on every worksheet change and reformat EVERYTHING.

Steve

Reply | Quote

Thanks Steve. I gave this a go but with no luck. I have attached a the spreadsheet instead of a screenshot.

Kerry

Reply | Quote

Hi Steve

Thanks for this info. I wasnt aware of this situation. As it turns out, I really dont need those other conditions anyway. I put in your original formula and it works just fine.

Thanks again

Have a good weekend!

Reply | Quote

Hi Steve

Thanks for this info. I wasnt aware of this situation. As it turns out, I really dont need those other conditions anyway. I put in your original formula and it works just fine.

Thanks again

Have a good weekend!

Reply | Quote