IF statement based on background color

Von Pookie

MrExcel MVP

Joined

Feb 17, 2002

Messages

13,686

• Jun 13, 2007

• #2

Not without a macro, no.

If the colors are set according to certain criteria, just write the IF statement to check for said criteria, not the coloring.

 

Upvote 0

Norie

Well-known Member

Joined

Apr 28, 2004

Messages

76,377

Office Version

1. 365

Platform

1. Windows

• Jun 13, 2007

• #3

How is the cell being coloured in the first place?

Manually or via conditional formatting?

 

Upvote 0

iggydarsa

Well-known Member

Joined

Jun 28, 2005

Messages

1,780

Office Version

1. 2019

Platform

1. Windows

• Jun 13, 2007

• #4

I dont know if there is a formula for that but you can use VBA:

Code:

Sub test()
    If Range("A1").Interior.Color = vbBlue Then
        'do this
    ElseIf Range("A1").Interior.Color = vbYellow Then
        'do that
    End If
End Sub

EDIT: I heard that in Office 2007 you can check background colors in formulas but not in previous versions.

 

Upvote 0

S

SeattleJim

New Member

Joined

Jun 4, 2007

Messages

20

• Jun 13, 2007

• #5

My spreadsheet uses both conditional formatting and data formatting. It works like a charm when users manually type in the information. But as soon as someone does a copy and paste, my main error catch routine doesn't work. The condition format still works (turns cell red when improper data is there). So instead of scanning over the spreadsheet looking for "red cells", there has to be an easier way to notify me when improper data has been copied to a cell.

 

Upvote 0

Scott Huish

MrExcel MVP

Joined

Mar 17, 2004

Messages

20,336

Office Version

1. 365

Platform

1. Windows

• Jun 13, 2007

• #6

iggydarsa:
That won't check for Conditional Formatting colors.

SeattleJim:
Why not just do a check for the same condition?
What is your Conditional Formatting checking for and in what range?

 

Upvote 0

S

SeattleJim

New Member

Joined

Jun 4, 2007

Messages

20

• Jun 13, 2007

• #7

I have about 40 columns of data... ranging from dates to addresses to correct state abbreviations. My condition formats check for no commas in addresses to length of zip code of only 5. I get the feeling that I may be SOL since this all works when manually typed in. But when I did a copy and paste from another spreadsheet, that is when my main error check and data validation won't work.

 

Upvote 0