Conditional Formatting Columns
 

Hi,
This is kind of a long problem so please bear with me for a minute.

I am trying to work with a dynamic worksheet that has no fixed column
or rows. Using VBA code, I want to evaluate each row for some specifi
value and if the current value is less than, say x, then cell content
are cleared.

Of the remaining values, I want to find the column max and format th
font. the code that I have does this. But the problem is:

If the column is empty then, the conditional format applies and whol
column gets formatted. How can I search for empty columns and appl
conditional format only if column is not empty or else skip th
column.

I would appreciate any help on this matter.

I have attached my code thus far.

Thanks again.

Sub modifyTable()

Dim cell As Range, minHH As Integer, Rng As Range, col As Range, NCo
As Integer, _
i As Integer


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 fo
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")
If .EntireColumn.Value < "" Then
'.Columns(False, i).Value < "" 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(1).Delete

End If
End With
Next i

Range("A1").Select
Application.ScreenUpdating = False

End Su

--
baner
-----------------------------------------------------------------------
banerg's Profile: http://www.excelforum.com/member.php...fo&userid=1533
View this thread: http://www.excelforum.com/showthread.php?threadid=26963

Conditional Formatting Columns
 

For i = 1 To NCol
With Range("HHData")
If Application.countA(.EntireColumm) < 0 Then

--
Regards,
Tom Ogilvy


"banerg" wrote in message
...

Hi,
This is kind of a long problem so please bear with me for a minute.

I am trying to work with a dynamic worksheet that has no fixed columns
or rows. Using VBA code, I want to evaluate each row for some specific
value and if the current value is less than, say x, then cell contents
are cleared.

Of the remaining values, I want to find the column max and format the
font. the code that I have does this. But the problem is:

If the column is empty then, the conditional format applies and whole
column gets formatted. How can I search for empty columns and apply
conditional format only if column is not empty or else skip the
column.

I would appreciate any help on this matter.

I have attached my code thus far.

Thanks again.

Sub modifyTable()

Dim cell As Range, minHH As Integer, Rng As Range, col As Range, NCol
As Integer, _
i As Integer


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")
If .EntireColumn.Value < "" Then
'.Columns(False, i).Value < "" 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(1).Delete

End If
End With
Next i

Range("A1").Select
Application.ScreenUpdating = False

End Sub


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