Formatting with formula

QuestionEdit

QUESTION: Is there any way to format a cell colour (background or text) based on the result of an IF function ie should the logical test be true return a cell reference in a set format, should logical test be false return a cell reference in original formatting?

I am using2002 SP3

ANSWER: Hi Dave How are you?

Yes, please try conditional formatting.

Adelaide

---------- FOLLOW-UP ----------

QUESTION: Thanks Adelaide,

Should have mentioned that! I am trying not to use conditional formatting as I am working on a large spreadsheet and when I try to copy/paste special/formats the conditional formatting is not transferring correctly.

I was hoping to include a formatting command within an IF formula.

AnswerEdit

Hi Dave How are you? Please consider the following VBA code:

Sub formattingwithFormula()

   Dim R As Range
   Set R = ActiveSheet.Range("A14")
   If R > 100 Then
       R.Interior.ColorIndex = 6
       Else
        R.Interior.ColorIndex = 3
   End If

End Sub

Adelaide