CODEWITHSUNDEEP
excelvba
mountain21
mountain21

Reputation: 41

Formatting a cell based on data in an another cell in same row

I want to identify (highlight) cells in column 4 of a worksheet that has the word "November" in the same row but is in column 1 of the same worksheet.

The code is partially successful as the macro does identify the word "November" in column 1 but the code will highlight the entire range of column 4, instead of just the cell in column 4 that is in the same row as "November".

Once I am able to get this code working I would like to change the text of the cell that gets highlighted in that row to "False".

So in practice, the word "November" would sit in cell A35 and the word "True" would sit in cell D35. I want the macro to highlight cell D35 and change the cell text to "False" because cell A35 is "November".

Sub Formatting()
    Dim Excel_File As Workbook
    Dim Tab_Report As Worksheet
    Dim tbl As ListObject
    Dim rng1 As Range
    Dim rng2 As Range

    Set Excel_File = ThisWorkbook
    Set Tab_Report = Excel_File.Worksheets("Tab_Report")
    Set tbl = Tab_Report.ListObjects("Excel_File")
    Set rng1 = tbl.ListColumns(1).DataBodyRange
    Set rng2 = tbl.ListColumns(4).DataBodyRange

    For Each cell In rng1
        If cell.Text = "November" Then rng2.Interior.Color = 11851260
    Next cell

End Sub

Upvotes: 0

Views: 109

Answers (1)

mountain21
mountain21

Reputation: 41

Credit goes to @SJR

Sub Formatting()

    Dim Excel_File As Workbook
    Dim Tab_Report As Worksheet
    Dim tbl As ListObject
    Dim rng As Range

    Set Excel_File = ThisWorkbook
    Set Tab_Report = Excel_File.Worksheets("Tab_Report")
    Set tbl = Tab_Report.ListObjects("Excel_File")
    Set rng = tbl.ListColumns(1).DataBodyRange


    For Each cell In rng
    If cell.Text = "November" Then cell.Offset(, 3).Interior.Color = 11851260
    Next cell

    For Each cell In rng
    If cell.Text = "November" Then cell.Offset(, 3) = "False"
    Next cell

End Sub

Upvotes: 1

Related Questions