Is it possible to only colour cells in a range with $ amount

[pedro-egoli]

Would like to Conditional Format a range , say E54:M54 , with only those cells with a $amount in them.

Everything I have tried ends with the whole range being coloured.

Perhaps it is not possible however, thought I would ask

Pedro

 

[crook_101]

Hi,

What is in the cell if it is NOT a $ ammount - I take it you are refering to currency when talking about $$s...

Ian

 

[mikerickson]

You can do this with a named function.

Select cell A1 and then define this name

Name: numForm
RefersTo: =GET.CELL(7, !A1)

Then select E54 and use the Conditional Formatting formula
=ISNUMBER(FIND("$",numForm))

Copy that formatting to the rest of the range. Any cell that is formatted for currency will have the Conditinal format applied.

 

[pedro-egoli]

Thanks Mike, will give that a try

Pedro

 

[pedro-egoli]

Mike eventually got the named range defined and followed your instructions.

Unfortunately when I copied across it highlighted all the cells E54:N54.

On my spreadsheet at the moment I have amounts in cells E54 and G54.
I only want these cells to be formatted (font/colour) and cell F54 which is BLANK, to remain blank.
As the spreadsheet expands I want future cells with amounts to be formatted and all BLANK cells to remain blank (with no colour)

Thanks

Pedro

 

[mikerickson]

When you copied the format, you copied both the currency number format and the Conditional formating, the highlighting is correct. Try changing the number format of one of the effected cells.
You may have to add a condition to keep blank cells with currency format un-highlited.

 

[pedro-egoli]

What condition would you suggest.

I tried "cell value is <1" and dragged it across and the whole range became highlighted again.
When I formatted G54as "number" prior to above cell went BLANK.

Thanks
Pedro

 

[mikerickson]

Define the name as above, select E54 and set two CF conditions.

Condition 1: Formula: =(E54="") Format: no fill, no border, font color automatic

Condition2: Formula: =ISNUMBER(FIND("$",numForm)) Format: your choice

 

[pedro-egoli]

Thanks for that Mike.

Works perfectly

Pedro

 

[pedro-egoli]

The suggestions on earlier part of thread are working fine.

However, I now wish to have font colour change if result is negative.

Tried the following

Condition 1=(G50=””)
Condition 2=ISNUMBER(FIND("$",numForm))
These 2 conditions colour cells as they are populated

Condition 3 =IF(G50<1,TRUE,FALSE)
With font colour to be Red if True
This condition is meant to colour font red

Cell is coloured correctly however, font remains black when result is negative.

Any suggestions

Pedro