conditional formatting based on column

[snax500]

In Excel2000, can I have a conditional format based on a formula
reference. For example, I have data in columns A and C. In column B I
have formulas that reference either column A or C ( +A15 or +C19). Can
Excel use conditional formatting to look at the formula and then format
it one way if in column A (red fill) and another in column C (yellow
fill).

Thanks

 

[zackb]

Hi there,

Think of Conditional Formatting much like that of a circular reference,
because that is what it is. You are attempting to give it a True condition
unto itself - and if everything lines up (is True) then the condition is met
and the format is applied.

I'll assume for a minute that we are talking about B15, where the formula in
this cell is "=A15".

Select B15
Format (menu) | Conditional Formatting | Formula Is ..
=B15=A15
Format as desired

So you see, it's less complicated to replicate the formula than it is to
reference the actual formula statement; which is possible, but (imo) more of
a pain than the alternative.

Is this what you are asking for?

 

[snax500]

Thanks but your formula doesn't work if both column A and C are equal.
Any other ideas.

 

[Bob Phillips]

Add the first condition of

=AND(A1=B1,B1=C1) and format accordingly, then extra conditions of
=A1=B1, yellow
and
=B1=C1, red

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[zackb]

Well, you can do a couple of things. One would be to use an older technique
that is a little tricky, but very doable. Follow these steps:

Hit Ctrl + F3
Name: TheFormula
Refers to: =GET.CELL(6,INDIRECT("RC",FALSE))
Hit Add
Hit Ok

Then select your cell in col B, goto Format (menu) | Conditional Formatting
| Formula Is ...
=TheFormula="="&ADDRESS(ROW(),COLUMN()-1,4)
This will check the formula in the cell in column B, if it says "=A1" (or
therow it is in) then the condition is true.

Another method contains using a UDF ...

Function MyFormula(Optional celRef As Range)
If celRef Is Nothing Then Set celRef = Application.Caller
MyFormula = celRef.Formula
End Function

Then use this conditional format for column B...
=MyFormula()="=A"&ROW()