1. ## Type mismatch help

Hi guys,

When I delete the content of the relevant cell, making it "" (empty), I get "Run time error '13': Type mismatch" on the bolded line of the vba...

A.) Can someone enlighten me why this might be?

Code:
```    If zRow = 4 Then                                                        'if all items sold make Seller name black..
'.......................................................................
Target.Offset(-2, -1).Font.Color = RGB(255, 255, 255)
If Target + Target.Offset(1) + Target.Offset(2) + Target.Offset(3) + Target.Offset(4) = 0 Then
Target.Offset(-2, -1).Font.Color = RGB(0, 0, 0)
End If
End If```
B.) Is there a way to describe this range in a shorter fashion? (works as it is, just interested in learning )

Code:
`If Target + Target.Offset(1) + Target.Offset(2) + Target.Offset(-1) + Target.Offset(-2) = 0 Then`

Thank you
Ferenc

2. You could try:

Code:
```Dim rng As Range
With Target
Set rng = Range(.Offset(-2), .Offset(2))
If WorksheetFunction.CountA(rng) = 0 Then
MsgBox "Hello"
End If
End With```
HTH,
Maud

3. Thank you Maudibe,

Originally Posted by Maudibe
You could try:

Code:
```Dim rng As Range
With Target
Set rng = Range(.Offset(-2), .Offset(2))
If WorksheetFunction.CountA(rng) = 0 Then
MsgBox "Hello"
End If
End With```
HTH,
Maud
This seems to work, save a few characters and make it a lot more uniformed for each row...

If WorksheetFunction.Sum(Range(Target.Offset(-2), Target.Offset(2))) = 0 Then

How would I make sure that this condition only goes through if the SUM is 0, but not Blank with the least amount of code?

4. Farenc,

Your formula will work only with numbers
If WorksheetFunction.Sum(Range(Target.Offset(-2), Target.Offset(2))) = 0 Then

My formula will work with both numbers and text
WorksheetFunction.CountA(Range(Target.Offset(-2), Target.Offset(2))) = 0 Then

You can simplify my formula the way I coded it above.

Your choice based on the type of data you are using.

HTH,
Maud

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•