How do I check formatting in an if-then statement

[bfmartiniv]

I want to build a spreadsheet that as I highlight a cell with a specific
color, I want the value in the cell replaced with a 1. How can I check that
the highlight color of a cell is a certain color? Can this be done with If
Then Logic?

 

[JE McGimpsey]

Is the value in the cell to be highlighted a calculated value? In that
case, use, for checking if the cell is blue (colorindex = 5)

=IF(COLORIND()=5,1,<your calculation here>)

where COLORIND is a User Defined Function:

Public Function ColorInd(Optional ByRef rng As Excel.Range)
Application.Volatile
If rng Is Nothing Then Set rng = Application.Caller
ColorInd = rng.Interior.ColorIndex
End Function

but since changing the color of a cell doesn't trigger calculation,
you'll have to fire it manually, or via another event.

If the cell to be highlighted contains a value instead, you'll have to
use a macro. Again, the problem is that there are no events that are
fired when you change the color of a cell. You could manually
recalculate, and use this event macro (put it in the worksheet code
module):

Private Sub Worksheet_Calculate()
If Range("A1").Interior.ColorIndex = 5 Then _
Range("B1").Value = 1
End Sub

If you're not familiar with UDFs and macros, see David McRitchie's
"Getting Started with Macros and User Defined Functions":

http://www.mvps.org/dmcritchie/excel/getstarted.htm