mercredi 29 mai 2019

If statement within look error 13 - type error

I have boolean or #N/A values in column DO. If a True boolean is within the DO column range, I wish for the corresponding cell in range r3FTop to be changed to the colour as per my code. My thinking is that DO2 cell corresponds to X5, and the D03 to X2.

When i run this code with Set rRng = Worksheets("mapOut").Range("DO2:DO2") and Set r3FTop = Worksheets("mapOut").Range("X5") the code works fine (cells turn to orange when the cell value at DO2 is True and don't when the cell value is False or #N/A). However upon adding the next cell in each range (DO3 and X2) I keep getting the type 13 error.

I've tried the bottom on the first row and it colours the cell at X5 perfectly. I run into problems when I try and add a second cell to the range.

For ease of use I have opted to batch the cell references I wish to colour in a group as they appear on screen.

Sub toCheckOut()

Dim rCell As Range
Dim rRng As Range
Dim r3FTop As Range

' check out check column
Set rRng = Worksheets("mapOut").Range("DO2:DO3")


Set r3FTop = Worksheets("mapOut").Range("X5", "X2")

For Each rCell In rRng.Cells
    If rRng.Value = False Then
        GoTo Continue

    ElseIf rRng.Value = True Then
        r3FTop.Interior.Color = RGB(237, 125, 49)

    Else
        GoTo Continue

    End If

Continue:

Next rCell

End Sub

My actual column range is DO2:DO90 and I have corresponding cell values for r3FTop variable too. I wish for the code to go though and disregard and #N/A or False values and only change corresponding cell colors when a True boolean is found.

I've tried rewriting the above code so that the True boolean is dealt with first as so with dealing with blanks (using application.worksheet.isna(rRng).

Any pointers would be greatly appreciated.

Aucun commentaire:

Enregistrer un commentaire