Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
snax500
 
Posts: n/a
Default conditional formatting based on column

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

  #2   Report Post  
zackb
 
Posts: n/a
Default

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?


--
Regards,
Zack Barresse, aka firefytr



"snax500" wrote in message
oups.com...
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



  #3   Report Post  
snax500
 
Posts: n/a
Default

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

  #4   Report Post  
Bob Phillips
 
Posts: n/a
Default

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)


"snax500" wrote in message
oups.com...
Thanks but your formula doesn't work if both column A and C are equal.
Any other ideas.



  #5   Report Post  
zackb
 
Posts: n/a
Default

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()


--
Regards,
Zack Barresse, aka firefytr


"snax500" wrote in message
oups.com...
Thanks but your formula doesn't work if both column A and C are equal.
Any other ideas.



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Some help with conditional formatting Jon Parker Excel Discussion (Misc queries) 1 April 18th 05 09:41 PM
Trying to add conditional formatting to every other cell...GOING . trixiebme Excel Worksheet Functions 2 March 24th 05 02:55 PM
Formula to compare multiple rows values based on another column? Murph Excel Worksheet Functions 4 February 21st 05 03:44 AM
Conditional formatting based on text Gilles Desjardins Excel Worksheet Functions 8 February 16th 05 11:45 PM
Conditional Formatting based on month name David Excel Discussion (Misc queries) 4 February 9th 05 10:19 AM


All times are GMT +1. The time now is 03:32 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"