CODEWITHSUNDEEP
excelvba
user10182078
user10182078

Reputation: 47

How to bold a cell based on the value?

How do I bold all cells that contain 1470?

I tried looping

Sub bold()
    Dim rng As Range, cell As Range
    Set rng = Range("j259:j291")
    For Each cell In rng
        If rng.Cells = "1470" Then
            rng.Cells.Font.bold = True
        End If
    Next cell
End Sub

I got the error

"Type mismatch"

Upvotes: 0

Views: 2096

Answers (2)

JvdV
JvdV

Reputation: 75840

Initial question

"Im looking for fastest way to bold all cell that contains value = "1470" by using vba"

This might be fast if you want to go with VBA instead of conditional formatting:

Sub bold()

Dim Rng As Range
Application.ReplaceFormat.Font.FontStyle = "Bold"
With ThisWorkbook.Sheets("Sheet1")
   Set Rng = .Range("J259:J291")
   Rng.Cells.Replace What:="1470", Lookat:=xlWhole, Replacement:="1470", SearchFormat:=False, ReplaceFormat:=True
End With

End Sub

Secundary question:

"addition to my question, if i want to make bold the entire row how can I possibly do it?"

In order to get your whole rows (or actually those cells you need) quickly bold you could use:

Sub bold()

Dim Rng As Range
With ThisWorkbook.Sheets("Sheet1")
Set Rng = .Range("A258:J291")
    Rng.AutoFilter Field:=10, Criteria1:="1470"
    Rng.Offset(1).Resize(Rng.Rows.Count - 1).SpecialCells(12).Font.bold = True
    Rng.AutoFilter
End With

End Sub

If you want the whole row bold you could also swap the Rng.Offset... line with:

Rows("259:291").SpecialCells(12).Font.bold = True

Adjust the variable Rng to suit your needs.

Upvotes: 1

Mikku
Mikku

Reputation: 6654

Problems:

  • Wrong use of Range
  • Incorrect way to check the Value of each cel

Advice:

  • Prefer not to use names that are already predefined in Excel, like Bold, Cell for variables or Module Name
  • Specify a Worksheet Name, Otherwise as currently it will work on the Activesheet

Try:

Sub bld()

    Dim cel As Range
        For Each cel In Range("J259:J291")
            If cel.Value = "1470" Then
                cel.Font.bold = True
            End If
        Next cel
End Sub

Upvotes: 1

Related Questions