Why compiler throws the error Type Mismatch

Question

The following function is to be done using this code snippet.

    ' If [b9] = "#N/A" Then
    ' If Range("B9").Value = "#N/A" Then
    ' If Range("B9") = #N/A Then
    Range("A9").Select
    With Selection.Interior
    .Color = -16776961
    End With

If the value in the cell B9 has #N/A then the cell A9 must be made red in colour.

The first three lines are the syntaxes that I have tried. None of them seem to work and the error Type Mismatch comes.

Read this https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba?r=SearchResults&s=1|270.3019 and see BigBen's answer. — SJR, Jan 03 '19 at 15:43
You could do it with _Conditional Formatting_ - no `VBA` required. Add `=ISNA($B$9)` to cell `A9` Conditional Formatting using _Use a formula to determine which cells to format_. — Darren Bartrup-Cook, Jan 03 '19 at 17:27

score 1 · Answer 1 · answered Jan 03 '19 at 15:35

1

Try,

If WorksheetFunction.IsNA(Range("B9").Value) Then
    Range("A9").Interior.Color = -16776961
End If

answered Jan 03 '19 at 15:35

BigBen

46,229
7
24
40

was about to post that as a single line! I tend to use Application, rather than WorkSheetFunction... does the error handling for each make a significant difference? – Cyril Jan 03 '19 at 15:35
@Cyril haha I thought about single line but personally prefer multi-line syntax, still yeah an easy solution. `Application` would be just fine as well, maybe preferable to `WorksheetFunction` depending on what OP wants. – BigBen Jan 03 '19 at 15:36

score 0 · Answer 2 · answered Jan 03 '19 at 19:48

0

A bit shorter than of BigBen's answer:

If [ISNA(B9)] Then...

answered Jan 03 '19 at 19:48

JohnyL

6,894
3
22
41

Why compiler throws the error Type Mismatch

2 Answers2