Colour using If statement

[Martin]

Hello,

I have the following calculation in a cell:

=IF(C28+C41 = 0, TREND(C9:C15,A9:A15,A16,1),C28+C41)

This is to use the trend function if the cell value is 0, this works fine.
What I would like to do however is to colour the font in red if the trend
function is being used but leave as black if there is actual data there i.e.
C28+C41 is not equal to 0.

I would really appreciate knowing how to do this.

Many thanks,

Martin

 

[Mike H]

Hi,

Use conditional formattting.

Select the cell with the formula in then:-

Format conditional formatting
Select 'Formula is'
enter the formula
=C28+C41=0

Pick a font colour

OK

Mike

 

[Martin]

Hi,

I have tried this and since made a change to the calculation which is now:

=IF(qryData!CH37 = 0, TREND(N22:N27,$A$22:$A$27,$A$28,1), qryData!CH37)

I tried this in the conditional formatting Formula Is section but no success.

Any further ideas?

I appreciate your help.

Martin

 

[Mike H]

Martin,

You can't directly use another sheet in conditional formatting so do it
indirectly. Somewhere on the formula sheet have a cell this in it
=qryData!CH37

The use that cell in your conditional format.
=Cell_address_on_This_sheet=0


Mike

 

[Chip Pearson]

Or, use a defined name to reference the cell on the other sheet.
Conditional Formatting accepts names that point to other sheets.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)