Subrata, you can try the following to format the column based on conditions.
Conditional Formatting Based on Column Values
Based on your query, I think you are trying to color the format of column C (from cell C1:C3) based on the following conditions:
1. If the cell value of Column A matches the cell value of Column B (here I have marked the cell in yellow color in the respective cell in column C).
2. If the cell value in Column A does not match with column B (here I have marked the cell in red color in the respective cell in column C).
3. Moreover, I have also considered that if there is no value in cell A and B column, i.e., a blank cell, then the difference will appear as blank in column C, and the respective cell will remain with no color.
Steps to Apply Conditional Formatting
To do the conditional format as mentioned above, please follow the steps below:
- Go to Home Menu → Select conditional format → Select "Use a formula to determine which cells to format."
- Put the format as per the following:
`=AND($A1=$B1,NOT(ISBLANK($A1)),NOT(ISBLANK($B1))) `
- Click the "Format" button and select the "Fill" color. Here I have selected "yellow" color. Press "Ok." In the "Applies to" area, put the formula as `=$D$1:$D$3`.
- Press New Rule. In the formula box, put the formula as: `=$A1<>$B1`. Select Format and repeat the above step. Here I have selected "red" color. Press "Ok." In the "Applies to" area, put the formula as `=$D$1:$D$3` and click on the "Apply" button.
Calculating the Difference in Column C
For calculating the difference of Column A & B, put the formula in Cell C1 as follows:
`=IF(OR(ISBLANK(A1),ISBLANK(B1)),"",A1-B1)`
The above formula will calculate the difference only if there are no blank cells; otherwise, cell values in Column C will remain blank, and no format will be applied.
To understand the above, please find attached herewith the Excel file. For any queries, feel free to write me: [Email Removed For Privacy Reasons] or visit my blog site:
Advanced Excel Tips - Manage your MIS Report.