Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
fix worksheet color
Hi:
How might I access a worksheet's background color in VBA? I know it is set by the monitor properties. I have a whole group of workbooks to fix, made by someone who likes lime green for their monitor color. Cheers, E |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
fix worksheet color
the background color is determined by the windows property (or as you call it
the monitor property). Any workbook that is opened should have the same background color unless somebody has done something like Cells.Interior.ColorIndex = 35 (in other words, they didn't change the background color, they changed the cell color for all the cells) in which case, that is how you would reverse it Cells.Interior.ColorIndex = xlNone -- Regards, Tom Ogilvy "canbya" wrote: Hi: How might I access a worksheet's background color in VBA? I know it is set by the monitor properties. I have a whole group of workbooks to fix, made by someone who likes lime green for their monitor color. Cheers, E |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
fix worksheet color
If you are referring to the cell fill color, then it would be:
Cells.Interior.ColorIndex = xlNone This would need done for each worksheet that is green. If you are talking about Windows Theme colors, then that is a whole different story and probably should not be tackled within Excel. Mike F "canbya" wrote in message oups.com... Hi: How might I access a worksheet's background color in VBA? I know it is set by the monitor properties. I have a whole group of workbooks to fix, made by someone who likes lime green for their monitor color. Cheers, E |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
fix worksheet color
Thanks for your help.
Cells.Interior.ColorIndex = xlNone This does not affect the green. Also, if I try to set it to white, I loose the gridlines, If you are talking about Windows Theme colors, then that is a whole different story and probably should not be tackled within Excel. Yes, I did reset this person's advanced appearance | window | color1 immediately upon catching this. But I am faced with 12 green workbooks with many worksheets each that cannot be regenerated (they were made from a live database several weeks ago). So do you know a way outside VB to fix these sheets? E |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
fix worksheet color
If i change that windows setting, it changes for all existing workbooks. (as
previously stated - has for all versions of excel and windows) Perhaps 1) that user went into format=Sheet , Background and imported a lime green bitmap. -- Regards, Tom Ogilvy "canbya" wrote: Thanks for your help. Cells.Interior.ColorIndex = xlNone This does not affect the green. Also, if I try to set it to white, I loose the gridlines, If you are talking about Windows Theme colors, then that is a whole different story and probably should not be tackled within Excel. Yes, I did reset this person's advanced appearance | window | color1 immediately upon catching this. But I am faced with 12 green workbooks with many worksheets each that cannot be regenerated (they were made from a live database several weeks ago). So do you know a way outside VB to fix these sheets? E |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
fix worksheet color
You could try changing the Normal Style to white fill and simulate gridlines
with grey borders. Sub NormalWhite() Dim bStyleNone As Boolean Dim bSystemWhite As Boolean Dim n As Long With ActiveWorkbook.Worksheets(1).Range("A1").Interior n = .ColorIndex If n 0 Then .ColorIndex = xlNone bSystemWhite = .Color = vbWhite If n 0 Then .ColorIndex = n End With With ActiveWorkbook.Styles("Normal") bStyleNone = .Interior.ColorIndex = xlNone If bSystemWhite < bStyleNone Then If bSystemWhite Then .Interior.ColorIndex = xlNone Else .Interior.Color = vbWhite End If With .Borders For n = 1 To 4 With .Item(n) If bSystemWhite Then .LineStyle = xlNone Else .LineStyle = xlContinuous .Weight = xlThin .Color = QBColor(7) 'or if sure default palette '.ColorIndex = 15 End If End With Next End With End If End With End Sub Run this on your 12 workbooks. Or if the file is to be exchanged between users with different monitor settings, install the code in each workbook and link to run in the workbook open event, it should 'toggle' the Style. The workbook must be active to change the normal style. Regards, Peter T "canbya" wrote in message oups.com... Thanks for your help. Cells.Interior.ColorIndex = xlNone This does not affect the green. Also, if I try to set it to white, I loose the gridlines, If you are talking about Windows Theme colors, then that is a whole different story and probably should not be tackled within Excel. Yes, I did reset this person's advanced appearance | window | color1 immediately upon catching this. But I am faced with 12 green workbooks with many worksheets each that cannot be regenerated (they were made from a live database several weeks ago). So do you know a way outside VB to fix these sheets? E |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
fix worksheet color
You might be on to something, Tom. If the FormatSheetbackground procedure
was used, you should be able to use the same process put a white (or color of preference) background in as a bitmap using a Paint file. "Tom Ogilvy" wrote: If i change that windows setting, it changes for all existing workbooks. (as previously stated - has for all versions of excel and windows) Perhaps 1) that user went into format=Sheet , Background and imported a lime green bitmap. -- Regards, Tom Ogilvy "canbya" wrote: Thanks for your help. Cells.Interior.ColorIndex = xlNone This does not affect the green. Also, if I try to set it to white, I loose the gridlines, If you are talking about Windows Theme colors, then that is a whole different story and probably should not be tackled within Excel. Yes, I did reset this person's advanced appearance | window | color1 immediately upon catching this. But I am faced with 12 green workbooks with many worksheets each that cannot be regenerated (they were made from a live database several weeks ago). So do you know a way outside VB to fix these sheets? E |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
fix worksheet color
If it is the background as Tom suggests, it will not print, so PrintPreview
should give you a white background. Does it ? NickHK "canbya" wrote in message oups.com... Thanks for your help. Cells.Interior.ColorIndex = xlNone This does not affect the green. Also, if I try to set it to white, I loose the gridlines, If you are talking about Windows Theme colors, then that is a whole different story and probably should not be tackled within Excel. Yes, I did reset this person's advanced appearance | window | color1 immediately upon catching this. But I am faced with 12 green workbooks with many worksheets each that cannot be regenerated (they were made from a live database several weeks ago). So do you know a way outside VB to fix these sheets? E |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
fix worksheet color
No go, to all the suggestions. Still the lime green.
It was too messy to make the cells white and add gridlines. There were border colors on some rows. I ended up restoring the databases and recreating. Gotta love backup. Thanks for your help. E NickHK wrote: If it is the background as Tom suggests, it will not print, so PrintPreview should give you a white background. Does it ? NickHK "canbya" wrote in message oups.com... Thanks for your help. Cells.Interior.ColorIndex = xlNone This does not affect the green. Also, if I try to set it to white, I loose the gridlines, If you are talking about Windows Theme colors, then that is a whole different story and probably should not be tackled within Excel. Yes, I did reset this person's advanced appearance | window | color1 immediately upon catching this. But I am faced with 12 green workbooks with many worksheets each that cannot be regenerated (they were made from a live database several weeks ago). So do you know a way outside VB to fix these sheets? E |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
fix worksheet color
Although the suggestion I posted indeed applies or removes borders to the
Normal style, it doesn't change any cell formats including borders. IOW, toggles white fill & grey borders on/off in the entire workbook, and automatically depending on users settings if required. The only inconsistency I can think of is if you apply a fill colour format normally that will hide Excel's gridlines, but the grey borders in the Normal style will persist. Apart from that I would have thought it was a simple and effective solution to the problem you described. Regards, Peter T "canbya" wrote in message ups.com... No go, to all the suggestions. Still the lime green. It was too messy to make the cells white and add gridlines. There were border colors on some rows. I ended up restoring the databases and recreating. Gotta love backup. Thanks for your help. E NickHK wrote: If it is the background as Tom suggests, it will not print, so PrintPreview should give you a white background. Does it ? NickHK "canbya" wrote in message oups.com... Thanks for your help. Cells.Interior.ColorIndex = xlNone This does not affect the green. Also, if I try to set it to white, I loose the gridlines, If you are talking about Windows Theme colors, then that is a whole different story and probably should not be tackled within Excel. Yes, I did reset this person's advanced appearance | window | color1 immediately upon catching this. But I am faced with 12 green workbooks with many worksheets each that cannot be regenerated (they were made from a live database several weeks ago). So do you know a way outside VB to fix these sheets? E |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CANNOT COLOR MY WORKSHEET TABS, TAB COLOR NOT IN FORMAT | Excel Worksheet Functions | |||
Color of worksheet Tab | Excel Programming | |||
worksheet background color | Excel Discussion (Misc queries) | |||
fill row color on a specified worksheet | Excel Programming | |||
fill row color on a specified worksheet | Excel Programming |