Sub modifyTable()
Dim cell As Range, minHH As Integer, _
Rng As Range, col As Range, NCol As Integer, _
i As Integer
Worksheets("Sheet1").Select
Range("A1").Select
With Worksheets("Sheet1").Range("B3")
Range(.Offset(1, 1), _
.Offset(1, 1).End(xlToRight).End(xlDown)) _
.Name = "HHData"
NCol = .Range(.Offset(1, 1), _
.Offset(1, 1).End(xlToRight)).Columns.Count
End With
MsgBox NCol
minHH = InputBox("Enter the minimum household" & _
" value desired for evaluation", _
"Data Modification")
For Each cell In Range("HHData")
If cell.Value <= minHH Then cell.ClearContents
If cell.Value = "" Then cell.Interior.ColorIndex = 15
Next
For i = 1 To NCol
With Range("HHData").Columns(i)
If Application.CountA(.EntireColumn.Value) <> 0 Then
.FormatConditions.Delete
.FormatConditions.Add Type:=xlCellValue, _
Operator:=xlEqual, _
Formula1:="=MAX(A:A)"
With .FormatConditions(1)
.Font.Bold = True
.Font.ColorIndex = 5
.Interior.ColorIndex = 6
End With
Else
.FormatConditions.Delete
End If
End With
Next i
Range("A1").Select
Application.ScreenUpdating = False
End Sub
Worked for me.
