.interior.color hates me

[Ken McLennan]

G'day there One & All,

Back once againg with something not working as I thought.

I have a list that I'm filtering by heading. I'm using the
Worksheet_SelectionChange event to check for the target cell, and then
filter by that criteria. Some of these criteria have a second sort
field, and what I'm trying to do is to alter the heading format to
indicate what's what.

Headings have black text and a light blue background. I want to
alter the format so that the main criteria is bold red text on a pink
back background, with the second sort criteria having the same colors,
but not bold.

The sort is working OK, and the font color is changing to red and
black as I click various cells, but the background color and the bold
property do nothing. No error is thrown, but the code just doesn't do
anything.

Here's what I have:

---[ CODE ] ---

Do some housekeeping,
turn screen updating off,
unprotect sheet,
and then:

This next bit sets base formatting for the header row and appears to
work OK.

With fltrCriteria.Cells
.Interior.Color = 16764057 ' LightBlue
.Font.Color = 0 ' Black
.Font.Bold = False
End With

# ("fltrCriteria" is a named range defining all header cells)

afterwards use the "target" from the sheet event:

Select Case target.Column
Case Is = 2 ' Station
' target.Activate

# (target.activate, and target.select have no effect)

With target.Cells
.Font.Color = 128 ' DarkRed
.Interior.Color = 8421631 ' Pink
.Font.Bold = True
End With
Set tmpCrit = fltrCriteria.Find(Work.Range("F12").Text)

# (determine secondary sort criteria)

With tmpCrit
.Font.Color = 128 ' DarkRed
.Interior.Color = 8421631 ' Pink
.Font.Bold = False
End With

srtShiftList obj_MAINFILTER:=target, obj_SECONDFILTER:=tmpCrit

# (srtShiftList is the sort routine in a standard module - if the
# optional obj_SECONDFILTER is not present then it sorts on a single
# criteria, and only one heading will be coloured red)

I've stepped through the code line by line, there are no errors,
the font.color seems to work OK, but everything else does nothing.
Google led to 3 or 4 different scenarios, but nothing that resolved the
issue. Does anyone have any ideas?

Thanks for listening.

 

[JE McGimpsey]

Your code works perfectly for me, assuming that the various ranges are
set correctly (e.g., that target is indeed in column 2, and that the
text value in F12 exists within fltrCriteria.

When you step through your sub, is the code that you gave executed?

Do you have any other event code running?

 

[Ken McLennan]

G'day there Mr McGimpsey,

Your code works perfectly for me, assuming that the various ranges are
set correctly (e.g., that target is indeed in column 2, and that the
text value in F12 exists within fltrCriteria.
Damn!!!

When you step through your sub, is the code that you gave executed?

It seems to be. The font color changes to red/black at appropriate
times, but no interior color change or bold property.

Do you have any other event code running?

I don't think so. There's a time display with a counter that
updates every 20 seconds, but I've got that turned off. I couldn't see
anything else that should be running.

I just tried to alter a target's interior color property from the
immediate window while the code was paused at a break point. That didn't
work either.

I then tried the same thing with another cell on the same sheet
and it worked OK from the immediate window.

I've just now tried to alter a cell with the address target.offset
(whatever, whatever) and that worked OK. The interior & bold properties
changed like they were supposed to.

Therefore the event is triggered, the code worked as required.
There must be something affecting the target cell properties. I'll keep
looking.

Thank you very much for your assistance. I might not have solved
the problem, but I'm sure that I'm headed in the right direction with a
running start and I'd not be that far without your help.

 

[Ken McLennan]

G'day there Mr McGimpsey,

I just tried to alter a target's interior color property from the
immediate window while the code was paused at a break point. That didn't
work either.

I then tried the same thing with another cell on the same sheet
and it worked OK from the immediate window.

I've just now tried to alter a cell with the address target.offset
(whatever, whatever) and that worked OK. The interior & bold properties
changed like they were supposed to.

I just had another play with it, and I've found that the
fltrCriteria range (all the headings) won't change interior color even
when I use the fill button on the toolbar to make it bright yellos. It
stays the same color (light blue) but when I look at the format box from
the right click menu, the sample on the 'Patterns' tab is bright yellow
and the 'Font' tab shows Bold. Something is amiss, but it's got me
stuffed.

 

[Peter T]

I just had another play with it, and I've found that the
fltrCriteria range (all the headings) won't change interior color even
when I use the fill button on the toolbar to make it bright yellos. It
stays the same color (light blue) but when I look at the format box from
the right click menu, the sample on the 'Patterns' tab is bright yellow
and the 'Font' tab shows Bold. Something is amiss, but it's got me
stuffed.

Conditional Formats, just a guess.

Regards,
Peter T

 

[JE McGimpsey]

Do you have a Conditional Format applied?

 

[Ken McLennan]

G'day there J McGimpsey & Peter T,

Do you have a Conditional Format applied?

Yes!!! That was it!!

There wasn't supposed to be a Conditional Format set, but I think
I deleted and added a row or two above my list ages ago and it's
possibly readjusted itself without my noticing. I'm not sure.

As it turned out, one of the conditional formats (simply rows of
alternating background colours) exactly matches the format for my header
row so I never noticed that it was conditional, not hard wired.

Thanks very much for your assistance. I doubt I'd have found it
without being pointed directly at it (even though I cursed & swore for
hours, I still couldn't see it. Maybe I should try a different problem
solving technique?)

Thanks again.