Rick excuse me for not getting back to this sooner.
I did some tests and found out where the error takes place - but I don't
know why or how to correct it. Here's the deal.
1. I tried your solution and it didn't work. It only works, as mine does,
if
I enter the desired value in the cell manually and press enter.
2. I closed the spreadsheet and tried only the proposed solution in a
brand
new spreadsheet. This works flawlessly.
3. Having the new file open, I re-opened my original spreadsheet and both
the original as the new file exhibit the same behaviour. Formatting
doesn't
take place not even in the new file.
Therefore my original spreadsheet "does" something to the environment that
affects every open spreadsheet.
After working with the debugger I found the following to take place, which
I
hope you can figure out.
[I put a break in the first line after the "If .column =3 then" so that I
can watch what happens.]
The "If .Value = True then" works for both TRUE and FALSE values.
** BUT **
As soon as I hit F8 in the ".Offset(0, -2).Resize(1,
2).Interior.ColorIndex
= xlNone" statement
(A) The yellow execution line pointer disappears
(B) The locals window in the debugger blanks out
(C) The execution obviously halts and control returns to the spreadsheet
Yet, if I manually press F2 and ENTER in the same cell as I was before and
Worksheet_Change execution halted, I get the same break in the debugger,
execution continues after the afore mentioned F8, interior coloring is
performed and the event runs past the EndIfs and closes normally.
I am aware that you cannot reproduce this behaviour.
Can you imagine what could stop the execution of the routine if it is
initiated by an InCell Data Validation DropDown Selection "path" whereas
nothing goes wrong after a direct (manual) value entry?
Is there a way to get some information from the debugger as to what goes
on
when I hit F8 and command execution stops? Mind you that the actual
command
is not executed. Termination takes place BEFORE execution - that's why the
cells don't become grey.
Thanks a lot in advance for any effort you make. I am at the end of my
rope
with this one.
Rick Rothstein said:
Actually, I get your code to work properly in a Change event procedure;
however, the clearing of the interior color isn't done correctly using
the
Color property. See if this code works (make sure you are putting it into
the correct Worksheet's code window)...
Private Sub Worksheet_Change(ByVal Target As Range)
With ActiveCell
If .Column = 3 Then
If .Value = True Then
.Offset(0, -2).Resize(1, 2).Interior.ColorIndex = xlNone
Else
.Offset(0, -2).Resize(1, 2).Interior.Color = RGB(128, 128, 128)
End If
End If
End With
End Sub