Conditional Formatting Help

[tonic1972]

Hi,

I want to change 4-5 cells black if one of the cells contains the work remitted and if that cell is blank change it red.

Can anyone help I have tried various ways and can only make the cell with remitted change colour..

Thanks :-0

Toni

 

[Special-K99]

4-5 cells.
What cell references?

Do you mean this?
Change a single cell to black if it contains the word "remitted" but if it is blank change it to red?

 

[tonic1972]

So I want to Change various lines (cells) in columns A-E to Black or Red dependent if Column D contains the word Remitted

Thanks

 

[Special-K99]

"Contains the word Remitted"
Contains or is equal to?
I'm assuming is equal to.

Select the area to highlight, e.g. A1:E100

Conditional Formatting
New Rule
Use a formula to determine...

=D1="Remitted"
format as black

=D1=""
format as red

 

[tonic1972]

I put contained as there is a date next to the word remitted, it has changed them all to red doing the above unless I have done it wrong.

I am new to this and cannot see how I can attach the spreadsheet to show you?

 

[Special-K99]

Instead of

=D1="Remitted"

try

=ISNUMBER(SEARCH("Remitted",D1))

 

[tonic1972]

Do I need two rules I have tried with one and it still doesnt change colour...

=ISNUMBER(SEARCH("",D))
=ISNUMBER(SEARCH("Remitted",D))

The spreadsheet is basically

Column A,B Text, Column C Amount, Column D Text

So 4 Columns across, 13 lines down.

It seems to change Column A only when I put that Formula in.

 

[Special-K99]

Yep 2 rules one for each colour.

You cant attach files on this forum.
Upload the file to an online storage site and post a link to it, though some people may not download the file for fear of viruses.

 

[Fluff]

Assuming your data stats in A2, select A2:E last row & use
=ISNUMBER(SEARCH("Remitted",$D2))
and with the same range selected
=$D2=""

 

[DanteAmor]

Hi @tonic1972, welcome to the forum"

Try this

1. Select the cells you want to format. ... (example: A2:E10)
2. On the Home tab, in the Styles group, click Conditional formatting > New Rule…
3. In the New Formatting Rule window, select Use a formula to determine which cells to format.
4. Enter the formula in the corresponding box.

=MATCH("*remitted*",$D2,0)

5. Click the Format… button to choose your custom format. (black color)

Repeat steps 1 to 4 and add this formula:

=$D2=""

5. Click the Format… button to choose your custom format. (red color)