Conditional Formatting: multiple conditions based on relative references

[Legacy 146544]

Hi all,

I am trying to apply conditional formatting to a large range ("C4:C1504").
The conditions for C4 are:
1)F4 should not be empty
2)C4 itself should not be 1
3)F4 should not equal (H4 + G4)
then 'something is wrong' and the number in C4 should be coloured red. The formatting of cell C4 is dependant of values in F4, H4, G4, etc... the formatting for C5 of F5, H5, G5... etc to 1500!

The tricky part if you ask me is: the relative references applying to the conditions.

I've tried this kind of formatting formula:
=IF(AND(INDIRECT(ADRESS(ROW();COLUMN()+3)<>"";INDIRECT(ADRESS(ROW();COLUMN())<>1;".....the third condition....."))

I took the idea from here:
http://www.mrexcel.com/forum/showthread.php?t=314030
A cool idea, I can't seem to make it work for myself though!


Anyone any suggestions?


Thanks a lot in advance,
Regards

 

[=ODIN=]

Unless I misunderstand, you should just be able to type the following in the conditional format for c4

=OR(F4="",C4=1,F4=H4+G4)

Then click copy on cell c4. Highlight the range that you want the same conditional format in and hit {alt, E, S, T, enter} (that key combo is the euivalent of doing a paste special formats.