Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting conditional formats based on condition
I have values in Range A5:A100 and conditional formats in
cells in range B5:B100. I need a code that will look at the last value in range A5:A100 and then delete all conditional formats ColumnB after the last value in Range A5:A100. For example if the last value in Range A5:A100 is in cell A15, then I need for all conditional formats to be removed from B16 and after. Another example... if the last value in Range A5:A100 is in cell A7, then I need for all conditional formats to be removed from B8 and after. Thanx todd Htutenstine |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting conditional formats based on condition
With Worksheets("Sheet3")
set rng = .Range(.Cells(.rows.count,1).end(xlup)(2), .Range("A100")) End With rng.offset(0,1).FormatConditions.Delete if they are being put in by your previous code, then just prevent them with Dim cellr As Range, res As Variant, res1 as Variant Worksheets(3).Activate For Each cellr In Worksheets(3).Range("A5:A100") if isempty(cellr) then exit for ' <=== res = Application.VLookup(cellr.Value, _ Worksheets(4).Range("M2:Q100"), 4, False) res1 = Application.VLookup(cellr.Value, _ Worksheets(4).Range("M2:Q100"), 5, False) If Not IsError(res) Then if instr(cellr.offset(0,1).NumberFormat,"%") then res = res * 100 res1 = res1 * 100 End if cellr.Offset(0, 1).Select cellr.Offset(0, 1).FormatConditions.Delete cellr.Offset(0, 1).FormatConditions.Add _ Type:=xlCellValue, _ Operator:=xlNotBetween, _ Formula1:=res, Formula2:=res1 cellr.Offset(0, 1).FormatConditions(1). _ Interior.ColorIndex = 3 End If Next -- Regards, Tom Ogilvy Todd Huttenstine wrote in message ... I have values in Range A5:A100 and conditional formats in cells in range B5:B100. I need a code that will look at the last value in range A5:A100 and then delete all conditional formats ColumnB after the last value in Range A5:A100. For example if the last value in Range A5:A100 is in cell A15, then I need for all conditional formats to be removed from B16 and after. Another example... if the last value in Range A5:A100 is in cell A7, then I need for all conditional formats to be removed from B8 and after. Thanx todd Htutenstine |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting conditional formats based on condition
Todd,
Try this Dim cLastRow As Long cLastRow = Cells(Rows.Count, "A").End(xlUp).Row Range("B" & cLastRow + 1 & ":B" & Rows.Count).FormatConditions.Delete -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Todd Huttenstine" wrote in message ... I have values in Range A5:A100 and conditional formats in cells in range B5:B100. I need a code that will look at the last value in range A5:A100 and then delete all conditional formats ColumnB after the last value in Range A5:A100. For example if the last value in Range A5:A100 is in cell A15, then I need for all conditional formats to be removed from B16 and after. Another example... if the last value in Range A5:A100 is in cell A7, then I need for all conditional formats to be removed from B8 and after. Thanx todd Htutenstine |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting conditional formats based on condition
Thanx
-----Original Message----- I have values in Range A5:A100 and conditional formats in cells in range B5:B100. I need a code that will look at the last value in range A5:A100 and then delete all conditional formats ColumnB after the last value in Range A5:A100. For example if the last value in Range A5:A100 is in cell A15, then I need for all conditional formats to be removed from B16 and after. Another example... if the last value in Range A5:A100 is in cell A7, then I need for all conditional formats to be removed from B8 and after. Thanx todd Htutenstine . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formats Date Based | Excel Discussion (Misc queries) | |||
Conditional Formating Based on Formats Instead of Values | Excel Discussion (Misc queries) | |||
Conditional Formatting (Hightlight row based on one condition) | Excel Discussion (Misc queries) | |||
conditional formats based on If | Excel Worksheet Functions | |||
how can I do more than 3 condition formats? | Excel Worksheet Functions |