Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
.interior.color hates me
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. -- See ya, Ken McLennan Qld, Australia |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
.interior.color hates me
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? In article , Ken McLennan wrote: G'day there One & All, Back once againg with something not working as I thought. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
.interior.color hates me
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. -- See ya, Ken McLennan Qld, Australia |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
.interior.color hates me
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. -- See ya, Ken McLennan Qld, Australia |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
.interior.color hates me
"Ken McLennan" wrote in message
... <snip 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
.interior.color hates me
Do you have a Conditional Format applied?
In article , Ken McLennan wrote: 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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
.interior.color hates me
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. -- See ya, Ken McLennan Qld, Australia |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
interior color of macro | Excel Programming | |||
Cell background color (interior color) setting not working | Excel Programming | |||
Interior color | Excel Programming | |||
Interior color | Excel Programming | |||
Passing Back Color to Interior Color | Excel Programming |