error message to solve & sheet refresh problem

[Simon Bailey]

Hi,

Hope someone can help - I am getting very frustrated!

My problem is in 2 parts:

1) I have the following code on a worksheet (found from a previous
question), that updates the cell/text colour when you enter a
specific letter.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Select Case Target.Value
Case "A"
Target.Interior.ColorIndex = 15
Target.Font.ColorIndex = 15
Case "V"
Target.Interior.ColorIndex = 36
Target.Font.ColorIndex = 36
Case "F"
Target.Interior.ColorIndex = 44
Target.Font.ColorIndex = 44
Case "D"
Target.Interior.ColorIndex = 6
Target.Font.ColorIndex = 6
Case "G"
Target.Interior.ColorIndex = 32
Target.Font.ColorIndex = 32
Case "N"
Target.Interior.ColorIndex = 37
Target.Font.ColorIndex = 37
End Select
End Sub

This works fine, until I select a range of cells and delete the data:
1) I get a "Runtime error '13' - Type mismatch error" and 2) the
cells stop the colour. How do I 1) change the code to solve the
error? 2) revert the cell to it's previous colour (not always the
same)?

2) I have another worksheet linked to the first and replicate the
information (alot of other info on sheet as well). I have got the
Formula to give me the correct letter as a result, but the colour
does not update, unless I do F2 enter on each individual cell (not
practical). I believe the issue is around the Worksheet_change
command, as I am not really changing the worksheet the data is just
refreshing - therefore what in my code needs to change? The above
issues also applies to this problem.

Many thanks, in advance, for any help - but please go easy, my VB
knowledge is limited

Simon

 

[Medemper]

A workaround for your second problem could be to have the first worksheet's change macro change the color on both sheets (assumes other sheet is named sheet2):

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 Then
OtherAddress = Target.Address
Select Case Target.Value
Case "A"
Target.Interior.ColorIndex = 15
Target.Font.ColorIndex = 15
Sheets("sheet2").Range(OtherAddress).Interior.ColorIndex = 15
Sheets("sheet2").Range(OtherAddress).Font.ColorIndex = 15
....

As far as deleting a group of cells, may have to make a separate macro for it (put in a module, not sheet code):

Sub DeleteRangeOfCells()
Dim ce As Range
For Each ce In Selection
ce.ClearContents
ce.Interior.ColorIndex = 0
ce.Font.ColorIndex = 1
Sheets("sheet2").Range(ce.Address).Interior.ColorIndex = 0
Sheets("sheet2").Range(ce.Address).Font.ColorIndex = 1
Next ce
End Sub


Hi,

Hope someone can help - I am getting very frustrated!

My problem is in 2 parts:

1) I have the following code on a worksheet (found from a previous
question), that updates the cell/text colour when you enter a
specific letter.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

Select Case Target.Value
Case "A"
Target.Interior.ColorIndex = 15
Target.Font.ColorIndex = 15
Case "V"
Target.Interior.ColorIndex = 36
Target.Font.ColorIndex = 36
Case "F"
Target.Interior.ColorIndex = 44
Target.Font.ColorIndex = 44
Case "D"
Target.Interior.ColorIndex = 6
Target.Font.ColorIndex = 6
Case "G"
Target.Interior.ColorIndex = 32
Target.Font.ColorIndex = 32
Case "N"
Target.Interior.ColorIndex = 37
Target.Font.ColorIndex = 37
End Select
End Sub

This works fine, until I select a range of cells and delete the data:
1) I get a "Runtime error '13' - Type mismatch error" and 2) the
cells stop the colour. How do I 1) change the code to solve the
error? 2) revert the cell to it's previous colour (not always the
same)?

2) I have another worksheet linked to the first and replicate the
information (alot of other info on sheet as well). I have got the
Formula to give me the correct letter as a result, but the colour
does not update, unless I do F2 enter on each individual cell (not
practical). I believe the issue is around the Worksheet_change
command, as I am not really changing the worksheet the data is just
refreshing - therefore what in my code needs to change? The above
issues also applies to this problem.

Many thanks, in advance, for any help - but please go easy, my VB
knowledge is limited

Simon

 

[BrianB]

I cannot see a way of avoiding the error, so have adapted the code t
trap and use it, as well as making changes for it to work on multipl
cell selection.

I dot not see a way of reverting back to the original cell format
because it will need to be stored somewhere. Perhaps you can refer to
cell with the format which is in a relative position to the target
This would just require a change in the Getout: code section.

Regarding making changes on sheet 2. Perhaps all you need to do is cop
this OnChange macro to there.

'==============================================
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
For Each c In Target.Cells
c.Interior.ColorIndex = CellColour(c.Value)
c.Font.ColorIndex = CellColour(c.Value)
Next
End Sub
'-- custom function
Function CellColour(MyVal As Variant)
On Error GoTo GETOUT
Select Case MyVal
Case "A"
CellColour = 15
Case "V"
CellColour = 36
Case "F"
MyFont = 44
Case "D"
CellColour = 6
Case "G"
CellColour = 32
Case "N"
CellColour = 37
End Select
Exit Function
GETOUT:
CellColour = 0
End Function
'============================================