CODEWITHSUNDEEP
excelconditional-formatting
user1834153
user1834153

Reputation: 330

How can I colour a cell in excel based on the values in the cell next to it?

I've looked in to conditional formatting, but it doesn't manage relative references at all.

what I would like to write is something like

 If ( the column number of this cell is more than the value in the first cell of this row) 
   colour it blue
end

It seems something really trivial, but writing something like:

=IF($C$3+4<=COLUMN();COLUMN()-4<=$D$3)

always returns true (I guess ecause it takes COLUMN as a fixed number, say the value it gets the frist time it calls it) and so all cells are blue.

that function returns the right value when evaluated inside the function field, but does not work for conditional formatting.

What can I do?

Upvotes: 0

Views: 94

Answers (3)

pnuts
pnuts

Reputation: 59460

Assuming you want something like this:

SO40679860 example

Select Columns A:H and HOME > Styles - Conditional Formatting, New Rule..., Use a formula to determine which cells to format and Format values where this formula is true::

=COLUMN()>$A1

Format..., select choice of formatting, OK, OK.

Upvotes: 1

keepAlive
keepAlive

Reputation: 6655

Quite hard to understand what you want without having more informations. Be that as it may, note that

=IF($C$3+4<=COLUMN();COLUMN()-4<=$D$3)

Means

if $C$3+4<=COLUMN() return COLUMN()-4<=$D$3 otherwise return FALSE

If it always returns TRUE it is because the tested cells are between the $C$3+4th and the $D$3+4th columns AND that $C$3 - $D$3 is a positive number which is sifficiently large for not beeing exceeded by the column number of the cells you test.

Upvotes: 0

Karpak
Karpak

Reputation: 1927

You can do this with the normal conditional formula itself. Perform the following steps.

  1. Click on a cell, the first cell that you wanted to color. Say it is C3
  2. Now the default cell becomes your cell C3.
  3. Click on Conditional formatting options, in that, select "Manage Rules"
  4. In Conditional formatting rule manage window, click on New Rule button
  5. In the new formatting Rule window. select Use a formula to determine which cells to format
  6. Enter =(COLUMN(C3) > $A3) in the Format values where this formula is true edit box
  7. Select the required color and click ok
  8. Now copy this cells to all other cells where you want to do the conditional formatting.

Here the step 1 and 2 are critical to ensure that it is relative.

Upvotes: 0

Related Questions