If statement dealing with color background

jjsevcik

New Member
Joined
Nov 19, 2009
Messages
5
I am trying to write an If statement that would search a column for cells that have red as a background color, and if they did, would mark the cell with an X. Does anyone know a formula I can use to accomplish this?

Thanks!
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

schielrn

Well-known Member
Joined
Apr 4, 2007
Messages
6,939
Welcome to the Board!

How is the cell populated with the color red? Is it through conditional formatting or just someone filling the cell manually?

If it is the latter you would need to use VBA to accomplish this.
 

jjsevcik

New Member
Joined
Nov 19, 2009
Messages
5
Thank you for the response. Yes, the cells are in fact filled with conditional formatting. Let me know if you need any additional information.
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690

ADVERTISEMENT

Use the same condition you have in CF to place an "X"
To really help, we need more info!
What causes the CF?
Do you want the "X" in the cell or next to it?

lenze
 

jjsevcik

New Member
Joined
Nov 19, 2009
Messages
5
The CF I am using is if the cell has a date that is less than today's date (is in the past or is "overdue"), then I color the cell red. I reference a cell with =TODAY() in the CF, and put Cell Value is less than the cell with the current date.
 

jjsevcik

New Member
Joined
Nov 19, 2009
Messages
5

ADVERTISEMENT

I would like the X in a seperate column altogether, it is not located next to the date.
 

schielrn

Well-known Member
Joined
Apr 4, 2007
Messages
6,939
Use an if statement to populate the X, but the X will not be there tomorrow as the today is not the same. Not sure if the X is suppose to stay or not, but you could try:

=if(A1=today(),"X","")

Change A1 tot he correct cell and input this formula where needed. If you need the X to stay you could maybe try:

=if(A1<=today(),"X","")

Hope that helps.
 

jjsevcik

New Member
Joined
Nov 19, 2009
Messages
5
That would work out perfect if I only had one conditional format, but I actually have 2 and am filling in some cells manually as well. I'm dealing with dates, when people are giving me updates...if a date has moved out more than one month, when I update the spreadsheet I manually make the cell red.

So, is there any way to solely look at a column, and write a formula to check the background color, and if it is red...return an X?
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690
If you Dates are in Column "A"
In CF, use "Formula Is"
Code:
Condition 1
=A2*(A2<(Today()-30)) 
'Format "RED"
Condition 2
=A2*(A2<Today())
'Format "Yellow" or whatever color you want
Conditon3
=A2
Format Green
Copy the Format down the column
Over 30 days will be red
Overdiue but less than 30 will be yellow
Current will be green
lenze
 

Watch MrExcel Video

Forum statistics

Threads
1,122,569
Messages
5,596,915
Members
414,110
Latest member
docops

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
Top