Macro To Identify Wrong Cells In Column

[Dazzawm]

Is there a macro I can run that will look at my column headers and if there are any cells that have anything in that column other than the word 'TRUE' it will tell me what cell that is in please. The column headers name will be in the format of UseDesc_1 and and the number will vary on the end maybe up to 20 or so. In can either colour in the column and I can look down it or a pop up box can tell me what cell anything but true is in. Thanks.

(There maybe other columns in the workbook with different header titles so it just needs to look at the columns with UseDesc_1 in)

I think it will need to be a macro rather than maybe an IF formula as there may be 10000 rows and 40 columns.

 

[VoG]

Hi. Try this

Sub Flag()
Dim LR As Long, i As Long, j As Long, Found As Range
Set Found = Rows(1).Find(what:="UseDesc_", LookIn:=xlValues, lookat:=xlPart)
If Found Is Nothing Then Exit Sub
j = Found.Column
LR = Cells(Rows.Count, j).End(xlUp).Row
For i = 2 To LR
    With Cells(i, j)
        If .Value <> True Then .Interior.ColorIndex = 6
    End With
Next i
End Sub

 

[Dazzawm]

Thanks VoG but that only worked on the first column there will be a different number after each UseDesc_

 

[VoG]

Try this

Sub Flag()
Dim LR As Long, LC As Long, i As Long, j As Long
LC = Cells(1, Columns.Count).End(xlToLeft).Column
For j = 1 To LC
    If Cells(1, j).Value Like "UseDesc_*" Then
        LR = Cells(Rows.Count, j).End(xlUp).Row
        For i = 2 To LR
            With Cells(i, j)
                If .Value <> True Then .Interior.ColorIndex = 6
            End With
        Next i
    End If
Next j
End Sub

 

[Dazzawm]

Thanks that works better, but can it ignore the blank cells?

 

[VoG]

Try

Sub Flag()
Dim LR As Long, LC As Long, i As Long, j As Long
LC = Cells(1, Columns.Count).End(xlToLeft).Column
For j = 1 To LC
    If Cells(1, j).Value Like "UseDesc_*" Then
        LR = Cells(Rows.Count, j).End(xlUp).Row
        For i = 2 To LR
            With Cells(i, j)
                If .Value <> True And .Value <> "" Then .Interior.ColorIndex = 6
            End With
        Next i
    End If
Next j
End Sub

 

[Dazzawm]

Sorry to be a pain VoG. Is there anyway it can colour the header cell as well so I know to look in that column, as I have as much as 10000 rows to look down for a highlighted cell!

 

[Dazzawm]

Sorry to be a pain VoG. Is there anyway it can colour the header cell as well so I know to look in that column, as I have as much as 10000 rows to look down for a highlighted cell!

 

[VoG]

Try

Sub Flag()
Dim LR As Long, LC As Long, i As Long, j As Long, Done As Boolean
LC = Cells(1, Columns.Count).End(xlToLeft).Column
For j = 1 To LC
    If Cells(1, j).Value Like "UseDesc_*" Then
        Done = False
        LR = Cells(Rows.Count, j).End(xlUp).Row
        For i = 2 To LR
            With Cells(i, j)
                If .Value <> True And .Value <> "" Then
                    If Not Done Then
                        Cells(1, j).Interior.ColorIndex = 3
                        Done = True
                    End If
                    .Interior.ColorIndex = 6
                End If
            End With
        Next i
    End If
Next j
End Sub

 

[Dazzawm]

Works great, thanks VoG.