fix worksheet color

  • Thread starter Thread starter canbya
  • Start date Start date
C

canbya

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
 
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
 
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
 
Thanks for your help.
Cells.Interior.ColorIndex = xlNone
Click to expand...

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.
Click to expand...

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
 
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.
 
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
 
You might be on to something, Tom. If the Format>Sheet>background 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.
 
If it is the background as Tom suggests, it will not print, so PrintPreview
should give you a white background.
Does it ?

NickHK
 
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
 
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
 
Back
Top