Conditional formatting based on reference cell value for each cell in row

Question

I would like to color cells in each row based on the value of a cell in the same row and then do the next one.

How do you set the formula to be able to color the cell if not equal to a reference cell on the same row?

So I just want to change the color of a different cell than reference cell which will have a different value for each row.

Solution 1

Given the input:

Select Cells B2:G6 and click on Conditional Formatting

Your prompt will look different on Windows, but the important part is "Use a formula to determine which cells to format" The formula is =B2<>$A2 Please note the lack of $ because it needs to be flexible (except for the reference cell column which in my case is A)

Final result is:

Solution 2

In Excel you will need to create a formatting rule, within the modal select the option that you want to use a formula to decide what cells to format:

Within the edit box you can type the formula that is used. The formula in the example will check whether the value of the 1 cell in the column is not empty. If this is true, the cell will be formatted as specified in the format preview.

Mainly using the ROW and COLUMN functions in Excel you can get access to the current Row and Column of the Cell for which the formula is executed.

Comments

[Manfred]

Just make sure you use absolute/relative cell references slavishly to make sure you can copy just the formatting to other cells as needed. Test it! Also in a text box where you can enter cell references (on Windows) you can use F4 to cycle through various forms of absolute/relative cell references.