Search code examples
excelconditional-formatting

Conditional Formatting with 3 different conditions

I've inherited an Excel file and need to add some conditional formatting; which I never used, so this is all kind of new to me. This needs to be kind of dynamic since different users will have different number of rows on their spreadsheet.

There are 24 columns on Sheet1. The conditional formatting should apply to columns 13 - 24. The conditional formatting should also apply from row 2 to row 200.

If column 1 is blank, columns 13 - 24 should have a white fill color

If column 1 is not blank, cells in column 13 - 24, should have a white fill color if there is an amount in any of these columns.

If column 1 is not blank, cells in column 13 - 24, should have a green fill color if these columns are blank.

I've tried to add rules using a formula but have not had any success.

Any help would be greatly appreciated. Thanks in advance

Solution
    1. Jump in to R1C1 mode in Excel.
    2. Select the cells that you want to have the conditional formatting.
    3. Add the rule =AND(NOT(ISBLANK(RC1)),ISBLANK(RC))
    4. Set the fill to Green
    5. Optionally revert back to the awful A1 mode.

    Screenshot