Excel / VB - How do I loop through each row/column and do formatting based on the value?

Question

Here's what I need to do:

1) Loop through every cell in a worksheet 2) Make formatting changes (bold, etc) to fields relative to each field based on the value

What I mean is that if a field has a value of "foo", I want to make the field that is (-1, -3) from it bold, etc. I tried to do this with the following script with no luck.

Thanks Johnny

Pseudo Code to Explain:

For Each Cell in WorkSheet
    If Value of Cell is 'Subtotal'
        Make the cell 2 cells to the left and 1 cell up from here bold and underlined
    End If
End ForEach

The Failed Macro (I don't really know VB at all):

Sub Macro2()
'
'
'
Dim rnArea As Range
Dim rnCell As Range

Set rnArea = Range("J1:J2000")

    For Each rnCell In rnArea
        With rnCell
            If Not IsError(rnCell.Value) Then
                Select Case .Value
                    Case "000 Total"
                        ActiveCell.Offset(-1, -3).Select
                        ActiveCell.Font.Underline = XlUnderlineStyle.xlUnderlineStyleSingleAccounting
                End Select
            End If
        End With
    Next
End Sub

Answer 1

Option Explicit

Private Sub macro2()
    Dim rnArea As Range
    Dim rnCell As Range

    ' you might need to change the range to the cells/column you want to format e. g. "G1:G2000" '
    Set rnArea = Range("J1:J2000")

    For Each rnCell In rnArea
        With rnCell
            If isBold(.Offset(1, 3).Value) Then
                .Font.Bold = True
            End If
            If isUnderlined(.Offset(1, 3).Value) Then
                'maybe you want this: .Font.Underline = xlUnderlineStyleSingle '
                .Font.Underline = xlUnderlineStyleSingleAccounting
            End If
        End With
    Next
End Sub

Private Function isBold(cellValue As Variant) As Boolean
    Dim myList() As Variant
    Dim listCount As Integer
    Dim i As Integer

    myList = Array("Totals", "FooTotal", "SpamTotal")
    listCount = 3

    isBold = False
    For i = 0 To listCount - 1
        If cellValue = myList(i) Then
            isBold = True
            Exit Function
        End If
    Next i
End Function

Private Function isUnderlined(cellValue As Variant) As Boolean
    Dim myList() As Variant
    Dim listCount As Integer
    Dim i As Integer

    myList = Array("FooTotal", "SpamTotal")
    listCount = 2

    isUnderlined = False
    For i = 0 To listCount - 1
        If cellValue = myList(i) Then
            isUnderlined = True
            Exit Function
        End If
    Next i
End Function

I added two functions but it should have also worked with an extensive if / else if / else.

Answer 2

Based on the comments on the solution above, i think this might be helpful

Sub FormatSpecialCells()
    Dim SearchRange As Range
    Dim CriteriaRange As Range

    Set SearchRange = Range("A2:A24")
    Set CriteriaRange = Range("C2:C5")

    Dim Cell As Range

    For Each Cell In SearchRange
     TryMatchValue Cell, CriteriaRange
    Next


End Sub

Private Sub TryMatchValue(CellToTest As Range, CellsToSearch As Range)
    Dim Cell As Range

    For Each Cell In CellsToSearch
        If Cell.Value = CellToTest.Value Then
            Cell.Copy
            CellToTest.PasteSpecial xlPasteFormats, xlPasteSpecialOperationNone, False, False
        End If
    Next
End Sub

This does not fully accomplish your goal. What it does is it searches a specified list of cells, and it matches them against a seperate list of cells. If it matches the values, it takes the FORMAT of the second list of cells and applies it to the cell it matched in the first list of cells. You can modify this by changing the TryMatchValue function so that instead of matching the CellToTest, it pastes the format onto another cell which is 2 across and one up.

This has the advantage that, if you want to add more values and different formats, you only need to go to your excel sheet and add more values. Also you only need to change the format on that value.

An example would be...

Have the cells you are searching in A1:D1000 Have these values in cells E2:E6... Subtotal (which is bold and underlined) Total (which is bold, underlined and italic) Net (which is bold underlined and Red) etc...

then when it hits Subtotal, it will change the cell to be bold and underlined. When it hits Total it will change the cell to be bold underlined and italic etc etc...

hope this helps

Answer 3

Would the conditional formatting functionality in excel give you what you need without having to write a macro?