Conditional Formatting Help

tonic1972

New Member
Joined
Oct 22, 2019
Messages
4
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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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?
 
Upvote 0
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
 
Upvote 0
"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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
Assuming your data stats in A2, select A2:E last row & use
=ISNUMBER(SEARCH("Remitted",$D2))
and with the same range selected
=$D2=""
 
Last edited:
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top