Conditional Formatting Question

  • Thread starter Thread starter mark.everett
  • Start date Start date
M

mark.everett

I don't know if this can even be done in Excel, and if it can, I don't
have a clue on how to do it, so I am asking the experts.

I have a rather extensive workbook that I use to configure Project
Server settings.

One page contains the security templates, which consists of columns of
data with the words "Yes" or "No" entered in text.

What I would like to do is have conditional formatting that would
change the cell background color if Yes changes to No or vice versa,
but otherwise, stay white. In other words, if the data in the cell
changes, change the background color.

Can this be done with conditional formatting or a macro?

Thanks in advance,
Mark
 
Hello Mark
This could be done both ways.
a)With conditional formating I would suggest :
1st condition, formula is
=AND($A1<>"";$A1="Yes")
format background colour as green for instance
2nd condition, formula is
=ET($A1<>"";$A1="No")
format background colour as red for instance

b)with an event macro (right-click onworkbook tab choose View code and paste
and amend accordingly):
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Value <> "" Then
Select Case Target.Value
Case Is = "Yes"
Target.Interior.ColorIndex = 4
Case Is = "No"
Target.Interior.ColorIndex = 3
Case Else: Target.Interior.ColorIndex = xlNone
End Select
End If
End Sub

HTH
Cordially
Pascal
 
Mark,

You can use Conditional Formatting to do what you want, but to do it would
require that you insert a copy of your table as a new section on your
original sheet, one that contains just the values of the table whose changes
you want to track.

Let's say your table is in cells A1:M100. Copy the table and pastespecial
values into cells AA1:AM100.

Then select cells A1:M100, and use Format / Conditional formatting...
select "Formula is" and use the formula

=A1<>AA1

Set your format, click OK, and then when cells in the range A1:M100 are
changed, they will change formatting.

You can also use the worksheet change event to do this, but this is simpler.

HTH,
Bernie
MS Excel MVP
 
Pascal and Bernie -

Thank you both very much. I will use your suggestions and settle on
the one that works best for me.

Thanks again!
Mark
 
Back
Top