Workaround to conditionally format for a range of cells in Excel

You can format cells in one column based on the value of cell in the same row in another column in Excel using conditional formatting but you have to workaround some quirks in the Excel Conditional Formatting Rules Manager (CFRM) user interface to get that result. Say that you want to format cells in column A based on the value of a cell in column B for the same row, i.e. A1 is depends on B1, A2 on B2, A3 on B3, etc. The steps for creating a conditional formatting rule for column A are as follows:

1. In Excel, select menu item Home, Conditional Formatting, Manage Rules.
2. In the CFRM dialog, press the New Rule button.
3. In the New Formatting Rule (NFR) dialog, select Use a formula to determine which cells to format.
4. In Format values where this formula is true, enter B1.
5. Set the format required.
6. Press the OK to close the NFR dialog.
7. Back in the CFRM dialog, you should see your new rule. In the Applies to field, enter $A:$A to use this rule for Column A. At this stage, if you press the OK button, the conditional format does not work for Column A. Use the following steps to workaround the
8. Press the Edit Rule button to display the Edit Formatting Rule (EFR) dialog.
9. In Format values where this formula is true, change ="B1" to =B1 (i.e. remove the double quotes) then press the button.
10. In the CFRM dialog, press the OK button.

After applying these steps, when a cell in Column B is TRUE, Excel applies the specified formatting to a cell in the same row in Column A.