# Issue with relative conditional formatting

#### MoogBot

##### New Member
I'm having issues with relative conditional formatting

i would like to highlight the current price of a list of stocks when it's below my target price.
I've learned that you need to use the INDIRECT formula to point to the relative current and target prices (if you don't do this than you always compare within the first line in the list)

My formula's are correct, returning false and true, but when i incorporate them in conditional formatting, it just doesn't work.

Can someone help out?

### Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Hi,

For first formula :
Code:
``=AND(W11>D11,W11>0)``

For second formula
Code:
``=AND(D11<=W11,W11<=D11*1.25)``

HTH

Hi James,

Thank you for the suggestion... However I think i already have formulas that should work. I included them in the sheet with a ' to show them -->

First condition (to be highlighted in green)
=AND(INDIRECT("D"&ROW())>=(INDIRECT("W"&ROW())),(INDIRECT("W"&ROW())>0))

Second condition (to be highlighted in yellow)
=AND((INDIRECT("W"&ROW()))>(INDIRECT("D"&ROW())),(INDIRECT("W"&ROW()))<=((INDIRECT("D"&ROW()))*1.25))

i'm not referencing directly, but using indirect, because apparently conditional formatting has issues with relative references when used in a list (column). I thought these formulas would fix the issue, however they do not format the cells.....

Hi,

From what I understand, there is no need for you to use Indirect() ...

Just apply the formulas to the whole list ... and relative references will adjust themselves ...

HTH

Hi James, You are correct.

Apparently, I was implementing it wrong by first applying conditional format to the first row and then adjusting the range to the list that it should apply to.

Replies
1
Views
303
Replies
2
Views
296
Replies
6
Views
590
Replies
0
Views
398
Replies
4
Views
280

1,196,152
Messages
6,013,742
Members
441,781
Latest member
Gian Carlos

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back