If statement dealing with color background

[jjsevcik]

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!

 

[schielrn]

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]

Thank you for the response. Yes, the cells are in fact filled with conditional formatting. Let me know if you need any additional information.

 

[schielrn]

What is the formula for the conditional formatting?

 

[lenze]

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]

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]

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

 

[schielrn]

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]

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]

If you Dates are in Column "A"
In CF, use "Formula Is"

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