Conditional formatting to a different cell and it contains a formula

[Rick]

This formula in B1 is: =SUBSTITUTE(A1,".","")
returns a number. (It removes a series of "dots" from the cell
contents in A1.)

Is it possible to have another cell C3 in that row become formatted,
if the contents of B1 are displaying the number 3?

So: A1 contains: " ............3" (without the quotes)
B1 displays the number 3
We'd like C3 to be bold, lets say, or purple if a 3 is displayed in
B1.

Thanks,
Rick

 

[Bob Phillips]

http://www.contextures.com/xlCondFormat01.html

 

[T. Valko]

So: A1 contains: " ............3" (without the quotes)

This formula in B1 is: =SUBSTITUTE(A1,".","")
B1 displays the number 3

The result in B1 is a *text* number not a *numeric* number.

You may want to change the formula to:

=--SUBSTITUTE(A1,".","")

This will coerce the text number to a *numeric* number.

Then, to apply the formatting:
Select cell C3
Goto the menu Format>Conditional Formatting
Select the Formula Is option
Enter this formula in the little box to the right:

=B1=3

Click the Format button
Select the desired style(s)
OK out

 

[Rick]

The result in B1 is a *text* number not a *numeric* number.

You may want to change the formula to:

=--SUBSTITUTE(A1,".","")

This will coerce the text number to a *numeric* number.

Then, to apply the formatting:
Select cell C3
Goto the menu Format>Conditional Formatting
Select the Formula Is option
Enter this formula in the little box to the right:

=B1=3

Click the Format button
Select the desired style(s)
OK out

--
Biff
Microsoft Excel MVP









- Show quoted text -

Thank you Biff and Bob. Will have a run at them in the AM, and report
back.

Rick

 

[RagDyer]

Click in C3, then, from the Menu Bar:
<Format> <Conditional Formatting>

Change "Cell Value Is" to "Formula Is", and enter this formula:

=B1="3"

Then click on <Format>
and choose whatever you like,
Then <OK> <OK>

The parenthesis around the 3 is necessary because your Substitute formula
returns a Text value, not a true number.

 

[RagDyer]

I just gotta refresh my OE more often!<bg>

 

[Rick]

I just gotta refresh my OE more often!<bg>
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !







- Show quoted text -

They all worked fine. You folks are great.
Rick.

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

I just gotta refresh my OE more often!<bg>
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------"RagDyer"







- Show quoted text -

They all worked fine. You folks are great.
Rick.