JimS
10-12-2009, 11:16 AM
I want to reference 2 different cells (that have Range Names associated to them) that the user will fill in.
These ranges will be used by the attached VBA to set Conditional Formatting and will also be used as Filter Criteria.
It doesn't like this line of code:
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(B1=lValue,NOT(ISBLANK(B1)))"
It actually puts "lValue" in the Conditional Format formula, not the Value in the Range Named "Low" cell.
The hValue works in this line though:
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:=hValue
After the user enters the values in the Range Named Cells (hValue and lValue) and runs the Macro the cells on Sheet1 are supposed to be filled in based on their value using Conditional Formatting. Then any cell that was filled in gets copied over to Sheet2.
Not sure how to refernce the Range Named Cells in the "CopyColors" routine either.
Also, not sure if there is a simply way to write this code (see below), open to any ideas.
Thanks...
JimS
Sub FillColors()
Dim hValue As String
Dim lValue As String
Application.ScreenUpdating = False
hValue = Range("High")
lValue = Range("Low")
Sheets("Sheet1").Select
Range("A1").Select
Columns("B:J").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(B1=lValue,NOT(ISBLANK(B1)))"
Selection.FormatConditions(1).Font.ColorIndex = 1
Selection.FormatConditions(1).Interior.ColorIndex = 4
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual _
, Formula1:=hValue
Selection.FormatConditions(2).Font.ColorIndex = 1
Selection.FormatConditions(2).Interior.ColorIndex = 3
Range("A1").Select
Run ("CopyColors")
End Sub
Sub CopyColors()
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("B1:J1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="<>0", Operator:=xlAnd, _
Criteria2:="<.075"
Columns("B:B").Select
Selection.Copy
Sheets("Sheet2").Select
Columns("B:B").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Selection.AutoFilter Field:=1
Selection.AutoFilter Field:=2, Criteria1:="<>0", Operator:=xlAnd, _
Criteria2:="<.075"
Columns("C:C").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Columns("C:C").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Selection.AutoFilter Field:=2
Selection.AutoFilter Field:=3, Criteria1:="<>0", Operator:=xlAnd, _
Criteria2:="<.075"
Columns("D:D").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Columns("D:D").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Selection.AutoFilter Field:=3
Selection.AutoFilter Field:=4, Criteria1:="<>0", Operator:=xlAnd, _
Criteria2:="<.075"
Columns("E:E").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Columns("E:E").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Selection.AutoFilter Field:=4
Selection.AutoFilter Field:=5, Criteria1:="<>0", Operator:=xlAnd, _
Criteria2:="<.075"
Columns("F:F").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Columns("F:F").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Selection.AutoFilter Field:=5
Selection.AutoFilter Field:=6, Criteria1:="<>0", Operator:=xlAnd, _
Criteria2:="<.075"
Columns("G:G").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Columns("G:G").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Selection.AutoFilter Field:=6
Selection.AutoFilter Field:=7, Criteria1:="<>0", Operator:=xlAnd, _
Criteria2:="<.075"
Columns("H:H").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Columns("H:H").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Selection.AutoFilter Field:=7
Selection.AutoFilter Field:=8, Criteria1:="<>0", Operator:=xlAnd, _
Criteria2:="<.075"
Columns("I:I").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Columns("I:I").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Selection.AutoFilter Field:=8
Selection.AutoFilter Field:=9, Criteria1:="<>0", Operator:=xlAnd, _
Criteria2:="<.075"
Columns("J:J").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Columns("J:J").Select
ActiveSheet.Paste
Rows("1:1").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("A1").Select
Sheets("Sheet1").Select
Rows("1:1").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("A1").Select
Application.ScreenUpdating = True
End Sub
These ranges will be used by the attached VBA to set Conditional Formatting and will also be used as Filter Criteria.
It doesn't like this line of code:
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(B1=lValue,NOT(ISBLANK(B1)))"
It actually puts "lValue" in the Conditional Format formula, not the Value in the Range Named "Low" cell.
The hValue works in this line though:
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual, Formula1:=hValue
After the user enters the values in the Range Named Cells (hValue and lValue) and runs the Macro the cells on Sheet1 are supposed to be filled in based on their value using Conditional Formatting. Then any cell that was filled in gets copied over to Sheet2.
Not sure how to refernce the Range Named Cells in the "CopyColors" routine either.
Also, not sure if there is a simply way to write this code (see below), open to any ideas.
Thanks...
JimS
Sub FillColors()
Dim hValue As String
Dim lValue As String
Application.ScreenUpdating = False
hValue = Range("High")
lValue = Range("Low")
Sheets("Sheet1").Select
Range("A1").Select
Columns("B:J").Select
Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
"=AND(B1=lValue,NOT(ISBLANK(B1)))"
Selection.FormatConditions(1).Font.ColorIndex = 1
Selection.FormatConditions(1).Interior.ColorIndex = 4
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreaterEqual _
, Formula1:=hValue
Selection.FormatConditions(2).Font.ColorIndex = 1
Selection.FormatConditions(2).Interior.ColorIndex = 3
Range("A1").Select
Run ("CopyColors")
End Sub
Sub CopyColors()
Rows("1:1").Select
Selection.Insert Shift:=xlDown
Range("B1:J1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=1, Criteria1:="<>0", Operator:=xlAnd, _
Criteria2:="<.075"
Columns("B:B").Select
Selection.Copy
Sheets("Sheet2").Select
Columns("B:B").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Selection.AutoFilter Field:=1
Selection.AutoFilter Field:=2, Criteria1:="<>0", Operator:=xlAnd, _
Criteria2:="<.075"
Columns("C:C").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Columns("C:C").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Selection.AutoFilter Field:=2
Selection.AutoFilter Field:=3, Criteria1:="<>0", Operator:=xlAnd, _
Criteria2:="<.075"
Columns("D:D").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Columns("D:D").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Selection.AutoFilter Field:=3
Selection.AutoFilter Field:=4, Criteria1:="<>0", Operator:=xlAnd, _
Criteria2:="<.075"
Columns("E:E").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Columns("E:E").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Selection.AutoFilter Field:=4
Selection.AutoFilter Field:=5, Criteria1:="<>0", Operator:=xlAnd, _
Criteria2:="<.075"
Columns("F:F").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Columns("F:F").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Selection.AutoFilter Field:=5
Selection.AutoFilter Field:=6, Criteria1:="<>0", Operator:=xlAnd, _
Criteria2:="<.075"
Columns("G:G").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Columns("G:G").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Selection.AutoFilter Field:=6
Selection.AutoFilter Field:=7, Criteria1:="<>0", Operator:=xlAnd, _
Criteria2:="<.075"
Columns("H:H").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Columns("H:H").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Selection.AutoFilter Field:=7
Selection.AutoFilter Field:=8, Criteria1:="<>0", Operator:=xlAnd, _
Criteria2:="<.075"
Columns("I:I").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Columns("I:I").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Selection.AutoFilter Field:=8
Selection.AutoFilter Field:=9, Criteria1:="<>0", Operator:=xlAnd, _
Criteria2:="<.075"
Columns("J:J").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Columns("J:J").Select
ActiveSheet.Paste
Rows("1:1").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("A1").Select
Sheets("Sheet1").Select
Rows("1:1").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("A1").Select
Application.ScreenUpdating = True
End Sub