Conditional Formatting: multiple conditions based on relative references

L

Legacy 146544

Guest
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=

Active Member
Joined
Dec 3, 2009
Messages
288
Unless I misunderstand, you should just be able to type the following in the conditional format for c4
Code:
=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.
 

Forum statistics

Threads
1,082,573
Messages
5,366,378
Members
400,887
Latest member
tporeda

Some videos you may like

This Week's Hot Topics

Top