I have successfully used the Cells.Interior.Color = X but in certain cases I
want to undo the change. I have tried Cells.Interior.Color = vbWhite but then
the cell border is gone.

Is there a way to set the color back to white (or automatic) and have the
cell borders show back up?

Thanks

Try this

Cells.Interior.ColorIndex = xlNone

Hope this helps! If so, let me know and click "YES" below.
--
Cheers,
Ryan


"extrapulp" wrote:

I have successfully used the Cells.Interior.Color = X but in certain cases I
want to undo the change. I have tried Cells.Interior.Color = vbWhite but then
the cell border is gone.

Is there a way to set the color back to white (or automatic) and have the
cell borders show back up?

Thanks

hi
instead of vbwhite which is true color, try ...
vbNone
or
xlNone.

both equate to "No Fill".

regards
FSt1

Regards
FSt1

"extrapulp" wrote:

I have successfully used the Cells.Interior.Color = X but in certain cases I
want to undo the change. I have tried Cells.Interior.Color = vbWhite but then
the cell border is gone.

Is there a way to set the color back to white (or automatic) and have the
cell borders show back up?

Thanks

I have the exact opposite problem. I have a workbook that is protected and I
need jsut the active cells to hightlight yellow so the customer can follow
the form. I applied the following code:

ActiveSheet.Unprotect Password:=€�xxxxx€�

'colors active cell yellow with blue outline

Cells.Interior.ColorIndex = xlNone

ActiveCell.Interior.ColorIndex = 27

ActiveSheet.Protect Password:=€�xxxxx€�

this worked great and the unprotected cell hightlight yellow and I no longer
got the 1004 error once I added the password code...now the problem I have...

This form has cells that are highlighted with other colors and when the code
about is applied it removes all colors except the active cell. I'm pretty
sure that its because of:

Cells.Interior.ColorIndex = xlNone

but Im not sure what to use so that other fields are not affected...any ideas?

Thanks,

Zina~





"RyanH" wrote:

Try this

Cells.Interior.ColorIndex = xlNone

Hope this helps! If so, let me know and click "YES" below.
--
Cheers,
Ryan


"extrapulp" wrote:

I have successfully used the Cells.Interior.Color = X but in certain cases I
want to undo the change. I have tried Cells.Interior.Color = vbWhite but then
the cell border is gone.

Is there a way to set the color back to white (or automatic) and have the
cell borders show back up?

Thanks

Zina

Try this version

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static OldCell As Range
If Application.CutCopyMode = 0 Then
ActiveSheet.Unprotect Password:="justme"
If Not OldCell Is Nothing Then
OldCell.Interior.ColorIndex = xlColorIndexNone
OldCell.Borders.LineStyle = xlLineStyleNone
End If
Set OldCell = Target
OldCell.Interior.ColorIndex = 6
OldCell.Borders.LineStyle = xlContinuous
Else
If OldCell Is Nothing Then
Set OldCell = Target
Else
Set OldCell = Union(OldCell, Target)
End If
End If
ActiveSheet.Protect Password:="justme"
End Sub

Will color the activecell yellow.

Note: will wipe out existing background color of activecell cell unless BG
color is due to CF


Gord Dibben MS Excel MVP

On Thu, 15 Jan 2009 14:08:11 -0800, Zina
wrote:

I have the exact opposite problem. I have a workbook that is protected and I
need jsut the active cells to hightlight yellow so the customer can follow
the form. I applied the following code:

ActiveSheet.Unprotect Password:=”xxxxx”

'colors active cell yellow with blue outline

Cells.Interior.ColorIndex = xlNone

ActiveCell.Interior.ColorIndex = 27

ActiveSheet.Protect Password:=”xxxxx”

this worked great and the unprotected cell hightlight yellow and I no longer
got the 1004 error once I added the password code...now the problem I have...

This form has cells that are highlighted with other colors and when the code
about is applied it removes all colors except the active cell. I'm pretty
sure that its because of:

Cells.Interior.ColorIndex = xlNone

but Im not sure what to use so that other fields are not affected...any ideas?

Thanks,

Zina~





"RyanH" wrote:

Try this

Cells.Interior.ColorIndex = xlNone

Hope this helps! If so, let me know and click "YES" below.
--
Cheers,
Ryan


"extrapulp" wrote:

I have successfully used the Cells.Interior.Color = X but in certain cases I
want to undo the change. I have tried Cells.Interior.Color = vbWhite but then
the cell border is gone.

Is there a way to set the color back to white (or automatic) and have the
cell borders show back up?

Thanks

H Gord,

That worked great! I do have one slight issue though...its now changing the
lines and borders in the sheet. any way to stop that and have no effect on
those?

THANK SO MUCH FOR YOUR HELP!

Zina~

"Gord Dibben" wrote:

Zina

Try this version

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static OldCell As Range
If Application.CutCopyMode = 0 Then
ActiveSheet.Unprotect Password:="justme"
If Not OldCell Is Nothing Then
OldCell.Interior.ColorIndex = xlColorIndexNone
OldCell.Borders.LineStyle = xlLineStyleNone
End If
Set OldCell = Target
OldCell.Interior.ColorIndex = 6
OldCell.Borders.LineStyle = xlContinuous
Else
If OldCell Is Nothing Then
Set OldCell = Target
Else
Set OldCell = Union(OldCell, Target)
End If
End If
ActiveSheet.Protect Password:="justme"
End Sub

Will color the activecell yellow.

Note: will wipe out existing background color of activecell cell unless BG
color is due to CF


Gord Dibben MS Excel MVP

On Thu, 15 Jan 2009 14:08:11 -0800, Zina
wrote:

I have the exact opposite problem. I have a workbook that is protected and I
need jsut the active cells to hightlight yellow so the customer can follow
the form. I applied the following code:

ActiveSheet.Unprotect Password:=€�xxxxx€�

'colors active cell yellow with blue outline

Cells.Interior.ColorIndex = xlNone

ActiveCell.Interior.ColorIndex = 27

ActiveSheet.Protect Password:=€�xxxxx€�

this worked great and the unprotected cell hightlight yellow and I no longer
got the 1004 error once I added the password code...now the problem I have...

This form has cells that are highlighted with other colors and when the code
about is applied it removes all colors except the active cell. I'm pretty
sure that its because of:

Cells.Interior.ColorIndex = xlNone

but Im not sure what to use so that other fields are not affected...any ideas?

Thanks,

Zina~





"RyanH" wrote:

Try this

Cells.Interior.ColorIndex = xlNone

Hope this helps! If so, let me know and click "YES" below.
--
Cheers,
Ryan


"extrapulp" wrote:

I have successfully used the Cells.Interior.Color = X but in certain cases I
want to undo the change. I have tried Cells.Interior.Color = vbWhite but then
the cell border is gone.

Is there a way to set the color back to white (or automatic) and have the
cell borders show back up?

Thanks

Delete the two lines dealing with oldcell.borders.linestyle

Those were "left-overs"


Gord

On Thu, 15 Jan 2009 17:48:01 -0800, Zina
wrote:

H Gord,

That worked great! I do have one slight issue though...its now changing the
lines and borders in the sheet. any way to stop that and have no effect on
those?

THANK SO MUCH FOR YOUR HELP!

Zina~

"Gord Dibben" wrote:

Zina

Try this version

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static OldCell As Range
If Application.CutCopyMode = 0 Then
ActiveSheet.Unprotect Password:="justme"
If Not OldCell Is Nothing Then
OldCell.Interior.ColorIndex = xlColorIndexNone
OldCell.Borders.LineStyle = xlLineStyleNone
End If
Set OldCell = Target
OldCell.Interior.ColorIndex = 6
OldCell.Borders.LineStyle = xlContinuous
Else
If OldCell Is Nothing Then
Set OldCell = Target
Else
Set OldCell = Union(OldCell, Target)
End If
End If
ActiveSheet.Protect Password:="justme"
End Sub

Will color the activecell yellow.

Note: will wipe out existing background color of activecell cell unless BG
color is due to CF


Gord Dibben MS Excel MVP

On Thu, 15 Jan 2009 14:08:11 -0800, Zina
wrote:

I have the exact opposite problem. I have a workbook that is protected and I
need jsut the active cells to hightlight yellow so the customer can follow
the form. I applied the following code:

ActiveSheet.Unprotect Password:=”xxxxx”

'colors active cell yellow with blue outline

Cells.Interior.ColorIndex = xlNone

ActiveCell.Interior.ColorIndex = 27

ActiveSheet.Protect Password:=”xxxxx”

this worked great and the unprotected cell hightlight yellow and I no longer
got the 1004 error once I added the password code...now the problem I have...

This form has cells that are highlighted with other colors and when the code
about is applied it removes all colors except the active cell. I'm pretty
sure that its because of:

Cells.Interior.ColorIndex = xlNone

but Im not sure what to use so that other fields are not affected...any ideas?

Thanks,

Zina~





"RyanH" wrote:

Try this

Cells.Interior.ColorIndex = xlNone

Hope this helps! If so, let me know and click "YES" below.
--
Cheers,
Ryan


"extrapulp" wrote:

I have successfully used the Cells.Interior.Color = X but in certain cases I
want to undo the change. I have tried Cells.Interior.Color = vbWhite but then
the cell border is gone.

Is there a way to set the color back to white (or automatic) and have the
cell borders show back up?

Thanks

Thanks Gord! Worked like a charm~!

"Gord Dibben" wrote:

Delete the two lines dealing with oldcell.borders.linestyle

Those were "left-overs"


Gord

On Thu, 15 Jan 2009 17:48:01 -0800, Zina
wrote:

H Gord,

That worked great! I do have one slight issue though...its now changing the
lines and borders in the sheet. any way to stop that and have no effect on
those?

THANK SO MUCH FOR YOUR HELP!

Zina~

"Gord Dibben" wrote:

Zina

Try this version

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static OldCell As Range
If Application.CutCopyMode = 0 Then
ActiveSheet.Unprotect Password:="justme"
If Not OldCell Is Nothing Then
OldCell.Interior.ColorIndex = xlColorIndexNone
OldCell.Borders.LineStyle = xlLineStyleNone
End If
Set OldCell = Target
OldCell.Interior.ColorIndex = 6
OldCell.Borders.LineStyle = xlContinuous
Else
If OldCell Is Nothing Then
Set OldCell = Target
Else
Set OldCell = Union(OldCell, Target)
End If
End If
ActiveSheet.Protect Password:="justme"
End Sub

Will color the activecell yellow.

Note: will wipe out existing background color of activecell cell unless BG
color is due to CF


Gord Dibben MS Excel MVP

On Thu, 15 Jan 2009 14:08:11 -0800, Zina
wrote:

I have the exact opposite problem. I have a workbook that is protected and I
need jsut the active cells to hightlight yellow so the customer can follow
the form. I applied the following code:

ActiveSheet.Unprotect Password:=€�xxxxx€�

'colors active cell yellow with blue outline

Cells.Interior.ColorIndex = xlNone

ActiveCell.Interior.ColorIndex = 27

ActiveSheet.Protect Password:=€�xxxxx€�

this worked great and the unprotected cell hightlight yellow and I no longer
got the 1004 error once I added the password code...now the problem I have...

This form has cells that are highlighted with other colors and when the code
about is applied it removes all colors except the active cell. I'm pretty
sure that its because of:

Cells.Interior.ColorIndex = xlNone

but Im not sure what to use so that other fields are not affected...any ideas?

Thanks,

Zina~





"RyanH" wrote:

Try this

Cells.Interior.ColorIndex = xlNone

Hope this helps! If so, let me know and click "YES" below.
--
Cheers,
Ryan


"extrapulp" wrote:

I have successfully used the Cells.Interior.Color = X but in certain cases I
want to undo the change. I have tried Cells.Interior.Color = vbWhite but then
the cell border is gone.

Is there a way to set the color back to white (or automatic) and have the
cell borders show back up?

Thanks