Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Trying to get VBA macro to be smarter

  Asked By: Matilda    Date: Sep 12    Category: MS Office    Views: 690
  

I just recently started dabbling in VBA macros in
Excel. I have the below macro created for a project that I am
currently working on. One problem that I can't seem to get past -
when I select a range of cells, and clear their contents, I get a
Run-time error '13' "type mismatch" error.

Any ideas how I could enable some logic to prevent this from
happening?



Private Sub Worksheet_Change(ByVal Target As Range)
Color_Cell Target
End Sub

Private Sub Color_Cell(ByVal Cell As Range)
If Cell.Value = "g" Then
Cell.Font.ColorIndex = 4
Cell.Interior.ColorIndex = 4
End If
If Cell.Value = "b" Then
Cell.Font.ColorIndex = 1
Cell.Interior.ColorIndex = 1
End If
If Cell.Value = "r" Then
Cell.Font.ColorIndex = 3
Cell.Interior.ColorIndex = 3
End If

End Sub

Share: 

 

4 Answers Found

 
Answer #1    Answered By: Cecelia Sims     Answered On: Sep 12

1) You did not include the last important bit of code where you select
the range  to be evaluated by Color_Cell.

So I am going to assume that your defined Range consists of more than
one cell, and it is defined as follows:
Dim Cell
Cell = Selection.Address

Since you have multiple cell  selected in your range, when you try and
evaluate Color_Cell, you can only evaluate the first cell in the range.
It would be better to loop through all cells  in the range Cell, and
evaluate each one on its own.

2) error  13 is a Type Mismatch error. For more information, search for
"trappable errors" in the VBE. That will explain it very well, and will
provide some insight on how to avoid it.

 
Answer #2    Answered By: Elliot Evans     Answered On: Sep 12

If you are only wanting to apply this to one cell  at a time, then in your
first macro  put, immediately following the "Private Sub... " line

If Target.Count >1 then exit sub

 
Answer #3    Answered By: Bes Massri     Answered On: Sep 12

That was the simple logic  that I wasn't
able to come up with. I love it when the simplest answer is the most
elusive!

 
Answer #4    Answered By: Naba Malik     Answered On: Sep 12

My 1st observation would be to exchange your if/then logic  to case/select.

vGetCellValue = Cell.Value
Select Case vGetCellValue
Case "g"
Cell.Font.ColorInde x = 4
Cell.Interior.ColorIndex = 4
Case "b"
Cell.Font.ColorInde x = 1
Cell.Interior.ColorIndex = 1
Case "r"
Cell.Font.ColorInde x = 3
Cell.Interior.ColorIndex = 3
Case Else
'default option
End Select

 
Didn't find what you were looking for? Find more on Trying to get VBA macro to be smarter Or get search suggestion and latest updates.




Tagged: