CODEWITHSUNDEEP
excelvba
honkin
honkin

Reputation: 113

Using IF in VBA

Can anyone tell me if this is the correct way to phrase this VBA macro?

The idea is for it to examine all cells in the range A2:A70 (actually, could be all of column A if it easier) and if the text of FA_Win_2 or FA_Win_3 is there, make the cell bold. 

If Range(“A2:A70”) = FA_Win_2 Or _
   Range(“A2:A70”) = FA_Win_3 Then
   Range(“A2:A70”).font.Bold = “True”
End If

I have tried different things like enclosing the FA_Win_2 in "", adding .Value after the range, but am always coming up with compile errors.

Any assistance would be much appreciated.

cheers

Upvotes: 1

Views: 75

Answers (2)

VBasic2008
VBasic2008

Reputation: 54948

Check Range for Values

For Next Loop

Sub ForNext()

    Dim i As Long   ' Source Column Range Cells Counter

    ' In Source Column Range
    With Range(A2:A70)
        ' Loop through cells of Source Column Range.
        For i = 1 To .Cells.Count
            ' Check for Search Criterias.
            If .Cells(i) = "FA_Win_2" Or .Cells(i) = "FA_Win_3" Then
                ' Apply formatting.
                .Cells(i).Font.Bold = True
            End If
        Next
    End With

End Sub

For Each Loop

Sub ForEach()

    Dim Cell As Range   ' Current Cell Range (For Each Control Variable)

    ' Loop through cells of Source Column Range.
    For Each Cell In Range("A2:A70")
        ' Check for Search Criterias.
        If Cell = "FA_Win_2" Or Cell = "FA_Win_3" Then
            ' Apply formatting.
            Cell.Font.Bold = True
        End If
    Next

End Sub

EDIT

For Each Loop 2

Sub ForEach2()

    Const cSheet As String = "Sheet3"   ' Source Worksheet Name
    Const cRange As String = "A2:A70"   ' Source Column Range Address
    Const cStr1 As String = "FA_Win_2"  ' Search Criteria 1"
    Const cStr2 As String = "FA_Win_3"  ' Search Criteria 2"

    Dim Cell As Range   ' Current Cell Range (For Each Control Variable)

    ' Loop through cells of Source Column Range in Source Worksheet.
    For Each Cell In ThisWorkbook.Worksheets(cSheet).Range(cRange)
        ' Check for Search Criterias.
        If Cell = cStr1 Or Cell = cStr2 Then
            ' Apply formatting.
            With Cell
                .Font.Bold = True
                .Font.Color = RGB(255, 255, 255)    ' White
                .Interior.Color = RGB(255, 0, 255)  ' Pink
            End With
        End If
    Next

End Sub

Upvotes: 1

urdearboy
urdearboy

Reputation: 14590

My approach would be to set the .Font.Bold = TRUE/FALSE equal to your test statement.

I'm not sure if you really only need to loop through this range, but you can easily update to make this more dynamic by swapping the 70 to a variable last row calc

Sub Test()

Dim i as Long

With ThisWorkbook.Sheets("Sheet1") '<-- Update
    For i = 2 To 70
        .Range("A" & i).Font.Bold = .Range("A" & i) = "FA_Win_2" Or .Range("A" & i) = "FA_Win_3"
    Next i
End With

End Sub

Upvotes: 1

Related Questions