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.

--
Regards,
Tom Ogilvy

"banerg" wrote in message
...

Thx Tom.

I tried what you suggested but it is not working. Any suggestions???

Thanks


--
banerg
------------------------------------------------------------------------
banerg's Profile:
http://www.excelforum.com/member.php...o&userid=15339
View this thread: http://www.excelforum.com/showthread...hreadid=269636