CODEWITHSUNDEEP
excelvba
briannn
briannn

Reputation: 23

Question regarding Excel VBA on invalid qualifier

I am currently working on a vba assignment...but I got stuck in this. It says there are invalid qualifiers and is unable to run. Please help and many thanks!!!!

Dim i As Integer
Dim RowCount As Integer
Dim category As String
Dim product As String
Dim quantity As String

With Sheets("Sheet1")
    RowCount = Cells(Rows.Count, 1).End(xlUp).Row

    ' Another way to count the number of rows needed

    Range("A1").Select
    category = ActiveCell.Offset(i, 0).Value
    product = ActiveCell.Offset(i, 1).Value
    quantity = ActiveCell.Offset(i, 2).Value

    For i = 2 To RowCount
        ' insert the IF/THEN/ELSE structure here to
        ' format the font color of Fruits to blue,
        ' and the rest of the others to magenta


    If category = "Fruits" Then
    category.Font.Color = vbBlue
    product.Font.Color = vbBlue
    quantity.Font.Color = vbBlue

    Else
    category.Font.Color = vbMagenta
    product.Font.Color = vbMagenta
    quantity.Font.Color = vbMagenta
    End If
    Next

End With

Upvotes: 1

Views: 210

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

use Ranges and Longs. Move the set into the loop.

Dim i As Long
Dim RowCount As Long
Dim category As Range
Dim product As Range
Dim quantity As Range

With Sheets("Sheet1")
    RowCount = .Cells(.Rows.Count, 1).End(xlUp).Row

    ' Another way to count the number of rows needed

    For i = 2 To RowCount
    Set category = .Cells(i, 1)
    Set product = .Cells(i, 2)
    Set quantity = .Cells(i, 3)
        ' insert the IF/THEN/ELSE structure here to
        ' format the font color of Fruits to blue,
        ' and the rest of the others to magenta


        If category = "Fruits" Then
            category.Font.Color = vbBlue
            product.Font.Color = vbBlue
            quantity.Font.Color = vbBlue
        Else
            category.Font.Color = vbMagenta
            product.Font.Color = vbMagenta
            quantity.Font.Color = vbMagenta
        End If
    Next

End With

or skip the variables:

With Sheets("Sheet1")
    Dim RowCount As Long
    RowCount = .Cells(.Rows.Count, 1).End(xlUp).Row

    Dim i As Long
    For i = 2 To RowCount
        ' insert the IF/THEN/ELSE structure here to
        ' format the font color of Fruits to blue,
        ' and the rest of the others to magenta

        If .Cells(i, 1) = "Fruits" Then
            .Range(.Cells(i, 1), .Cells(i, 3)).Font.Color = vbBlue
        Else
            .Range(.Cells(i, 1), .Cells(i, 3)).Font.Color = vbMagenta
        End If
    Next

End With

Upvotes: 1

Related Questions