Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional formatting columns | Excel Discussion (Misc queries) | |||
Conditional Formatting - 2 columns | Excel Worksheet Functions | |||
conditional formatting- same value, different columns | Excel Discussion (Misc queries) | |||
conditional formatting for key-value columns | New Users to Excel | |||
Conditional Formatting 4 Columns | Excel Discussion (Misc queries) |