Archive of Mr Excel Message Board

Back to Forms in Excel VBA archive index
Back to archive home

conditional color change

Posted by Andonny on April 21, 2001 7:54 PM

Hi,
I would like to change the background color to red in A1 and B1 if in C1 is "changed". Maybe a formula in D1 would do the trick but I don't know how the formula should look like.

........A........B..........C.........D
1....pear...pears....changed.........

Thank you very much for your help
Andonny

Re: conditional color change

Posted by Aladin Akyurek on April 21, 2001 9:58 PM

Hi Andonny

Select A1 and B1, activate Format|Conditional Formatting, choose "Formula is", and type

=$C1="changed"

Then activate Format|Patterns, choose read for Cell Shading.

Aladin

Re: found code - could be modified

Posted by Andonny on April 21, 2001 10:22 PM

Hi,
The only thing I would need to include is that the cell in culumn A changes to yellow but how?

Sub Insert_1_intoA_ifChanged_in_C()
Dim RowNum As Integer
Dim LastRow, LastCol As Integer

With Worksheets("Sheet1")
LastRow = .UsedRange.Rows.Count
LastCol = .UsedRange.Columns.Count

For RowNum = LastRow To 1 Step -1
If Range("C" & RowNum) = "changed" Then
Range("A" & RowNum).FormulaR1C1 = "1" (yellow here as well)

End If
Next RowNum
End With
End Sub

Re: conditional color change (Thanks a million)

Posted by Andonny on April 22, 2001 12:13 AM

HiAladin,
Thank you very much for your tip. It works perfectly for me

Take care
Andonny

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.