Conditional formatting
I'm having trouble setting conditional formats with VBA.
I currently have: With Worksheets(OPG).Range("D8").FormatConditions.Add(x lCellValue, xlNotBetween, "=VALUE(LEFT(C8,4))", "=VALUE(RIGHT(C8,4))") With .Font .ColorIndex = 3 End With End With This gives Subscript out of range on the first line. C8 contains 2.94-3.06 and is a range of accepable values. Ian --- |
Conditional formatting
Hi Ian,
Try something like: Sub Tester() Dim rng As Range Dim var1 As Variant, var2 As Variant Dim sh As Worksheet Set sh = Worksheets(OPG) Set rng = sh.Range("C8") var1 = CDbl(Left(rng.Value, 4)) var2 = CDbl(Right(rng.Value, 4)) With rng(1, 2).FormatConditions .Delete .Add _ Type:=xlCellValue, _ Operator:=xlBetween, _ Formula1:=var1, _ Formula2:=var2 .Item(1).Font.ColorIndex = 46 End With End Sub --- Regards, Norman "Ian" wrote in message ... I'm having trouble setting conditional formats with VBA. I currently have: With Worksheets(OPG).Range("D8").FormatConditions.Add(x lCellValue, xlNotBetween, "=VALUE(LEFT(C8,4))", "=VALUE(RIGHT(C8,4))") With .Font .ColorIndex = 3 End With End With This gives Subscript out of range on the first line. C8 contains 2.94-3.06 and is a range of accepable values. Ian --- |
Conditional formatting
Hi Ian,
Additionaaly, if the worksheet name is OPG, then the line: Set sh = Worksheets(OPG) should be: Set sh = Worksheets("OPG") --- Regards, Norman "Norman Jones" wrote in message ... Hi Ian, Try something like: Sub Tester() Dim rng As Range Dim var1 As Variant, var2 As Variant Dim sh As Worksheet Set sh = Worksheets(OPG) Set rng = sh.Range("C8") var1 = CDbl(Left(rng.Value, 4)) var2 = CDbl(Right(rng.Value, 4)) With rng(1, 2).FormatConditions .Delete .Add _ Type:=xlCellValue, _ Operator:=xlBetween, _ Formula1:=var1, _ Formula2:=var2 .Item(1).Font.ColorIndex = 46 End With End Sub --- Regards, Norman "Ian" wrote in message ... I'm having trouble setting conditional formats with VBA. I currently have: With Worksheets(OPG).Range("D8").FormatConditions.Add(x lCellValue, xlNotBetween, "=VALUE(LEFT(C8,4))", "=VALUE(RIGHT(C8,4))") With .Font .ColorIndex = 3 End With End With This gives Subscript out of range on the first line. C8 contains 2.94-3.06 and is a range of accepable values. Ian --- |
Conditional formatting
Hi Norman
I tried your code, but it still fails. I get the same error as before, but it appears on the "Set sh" line. The sheet name is definitely correct and I tried it with the original designation (Sheet6), but get a type mismatch error. At least your method narrows it down, but I'm still unable to figure out what's happening. -- Ian -- "Norman Jones" wrote in message ... Hi Ian, Try something like: Sub Tester() Dim rng As Range Dim var1 As Variant, var2 As Variant Dim sh As Worksheet Set sh = Worksheets(OPG) Set rng = sh.Range("C8") var1 = CDbl(Left(rng.Value, 4)) var2 = CDbl(Right(rng.Value, 4)) With rng(1, 2).FormatConditions .Delete .Add _ Type:=xlCellValue, _ Operator:=xlBetween, _ Formula1:=var1, _ Formula2:=var2 .Item(1).Font.ColorIndex = 46 End With End Sub --- Regards, Norman "Ian" wrote in message ... I'm having trouble setting conditional formats with VBA. I currently have: With Worksheets(OPG).Range("D8").FormatConditions.Add(x lCellValue, xlNotBetween, "=VALUE(LEFT(C8,4))", "=VALUE(RIGHT(C8,4))") With .Font .ColorIndex = 3 End With End With This gives Subscript out of range on the first line. C8 contains 2.94-3.06 and is a range of accepable values. Ian --- |
Conditional formatting
Brilliant, thanks Norman. This was alos the problem with my original line.
All working well, now. -- Ian -- "Norman Jones" wrote in message ... Hi Ian, Additionaaly, if the worksheet name is OPG, then the line: Set sh = Worksheets(OPG) should be: Set sh = Worksheets("OPG") --- Regards, Norman "Norman Jones" wrote in message ... Hi Ian, Try something like: Sub Tester() Dim rng As Range Dim var1 As Variant, var2 As Variant Dim sh As Worksheet Set sh = Worksheets(OPG) Set rng = sh.Range("C8") var1 = CDbl(Left(rng.Value, 4)) var2 = CDbl(Right(rng.Value, 4)) With rng(1, 2).FormatConditions .Delete .Add _ Type:=xlCellValue, _ Operator:=xlBetween, _ Formula1:=var1, _ Formula2:=var2 .Item(1).Font.ColorIndex = 46 End With End Sub --- Regards, Norman "Ian" wrote in message ... I'm having trouble setting conditional formats with VBA. I currently have: With Worksheets(OPG).Range("D8").FormatConditions.Add(x lCellValue, xlNotBetween, "=VALUE(LEFT(C8,4))", "=VALUE(RIGHT(C8,4))") With .Font .ColorIndex = 3 End With End With This gives Subscript out of range on the first line. C8 contains 2.94-3.06 and is a range of accepable values. Ian --- |
All times are GMT +1. The time now is 01:13 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com