Selected Cell Problem

[Shazi]

Dear All,

I am using the below worksheet event to increase the Selected Font
Size and to change the Interior Color (background color).

This procedure is working fine, but there is a small problem, its
removing all Filled Color area from (background area) and making Font
10 in all sheet.

I want a procedure to change the Font Size and Back color only in the
selected cell. rest of the sheet will remain same formatting.

If its possible pls send me reply.

My procedure is given below.

'+++++++++++++++++++++++++++++++++++++++++++++++++++++
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Application.ScreenUpdating = False

Cells.Interior.ColorIndex = xlNone
Cells.Font.Size = 10
Target.Interior.ColorIndex = 8

With Selection.Font
.Name = "Arial"
.Size = 20
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With

Application.ScreenUpdating = True

End Sub

'++++++++++++++++++++++++++++++++++++++++++++

Thanks and regards.

Shahzad Zafar
Madinah

 

[Don Guillett]

I answered this in a post to you yesterday.........
Public lastcell
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'MsgBox lastcell
On Error Resume Next
With Range(lastcell)
.Interior.ColorIndex = xlNone
.Font.Size = 10
End With

With Target
.Font.Size = 18
.Interior.ColorIndex = 8
End With

lastcell = Target.Address
End Sub

 

[JLGWhiz]

These two lines of your code remove the background color and set the Font
size to 10 for all cells on the active sheet.

Cells.Interior.ColorIndex = xlNone
Cells.Font.Size = 10

If you delete these two lines, your problem will go away.

 

[Gord Dibben]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static OldCell As Range
If Application.CutCopyMode = 0 Then
Application.ScreenUpdating = False
Set OldCell = Target
With OldCell
.Interior.ColorIndex = 8
.Font.Size = 20
.Font.Name = "Arial"
End With
Application.ScreenUpdating = True
End If
End Sub


Gord Dibben MS Excel MVP

 

[Rick Rothstein \(MVP - VB\)]

I think this code will do what you are looking for...

Dim LastFontSize As Long
Dim LastColorIndex As Long
Dim LastVisitedCell As String

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.ScreenUpdating = False
If Len(LastVisitedCell) > 0 Then
Range(LastVisitedCell).Interior.ColorIndex = LastColorIndex
Range(LastVisitedCell).Font.Size = LastFontSize
End If
LastColorIndex = Target.Interior.ColorIndex
LastFontSize = Target.Font.Size
Target.Interior.ColorIndex = 8
With Selection.Font
.Name = "Arial"
.Size = 20
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Application.ScreenUpdating = True
LastVisitedCell = Target.Address
End Sub

Rick

 

[Shazi]

I think this code will do what you are looking for...

Dim LastFontSize As Long
Dim LastColorIndex As Long
Dim LastVisitedCell As String

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Application.ScreenUpdating = False
  If Len(LastVisitedCell) > 0 Then
    Range(LastVisitedCell).Interior.ColorIndex = LastColorIndex
    Range(LastVisitedCell).Font.Size = LastFontSize
  End If
  LastColorIndex = Target.Interior.ColorIndex
  LastFontSize = Target.Font.Size
  Target.Interior.ColorIndex = 8
  With Selection.Font
    .Name = "Arial"
    .Size = 20
    .Strikethrough = False
    .Superscript = False
    .Subscript = False
    .OutlineFont = False
    .Shadow = False
    .Underline = xlUnderlineStyleNone
    .ColorIndex = xlAutomatic
  End With
  Application.ScreenUpdating = True
  LastVisitedCell = Target.Address
End Sub

Rick




















- Show quoted text -

Dear All,

Thank you very much for your continious support specially to Mr. Rick.
Now I got the solution of my problem exactly what I want.

Once again thank you for taking time for me to solve my problem.

by the way Mr. Rick I am using your procedure in my project.

with best regards.

Syed Shahzad Zafar
Madinah