CODEWITHSUNDEEP
excelconditional-formatting
OwenS
OwenS

Reputation: 233

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

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.

Upvotes: 2

Views: 17896

Answers (3)

Chrismas007
Chrismas007

Reputation: 6105

Given the input:

Before

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)

Add Rule

Final result is:

After

Upvotes: 1

SeanC
SeanC

Reputation: 15923

Conditional formatting foes not need to operate on the cell you are formatting. Using a mixture of $ and non-$ formatted formulas, you can set highlighting accordingly.

Note to make sure this works correctly, highlight the area to be formatted first, and note where you start. e.g. If I wanted this to apply to A2:A50, then I would highlight A2 first, and make the formula reference row 2.

Also, note that you will have to type in the formula. Any clicking of cells will automatically put in $ signs to fix the comparison to that specific location, which is not the behavior we desire

e.g. To format A2:K50, when the H value in that column is 7
Highlight A2:K50
Conditional Formatting -> New Rule -> Use a formula
formula would be =($H2=7) <- this will fix the column to be H, and vary the row accordingly
Format as desired

e.g.2. To format based on previous cell
Highlight A2:A50
Conditional Formatting -> New Rule -> Use a formula
formula would be =(A2<>A1) <- this will check the row above each time Format as desired

Upvotes: -1

RFerwerda
RFerwerda

Reputation: 1277

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:

enter image description here

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.

Upvotes: 2

Related Questions