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

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

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

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

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

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

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

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

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

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