Conditional formatting code wont work

[Robert Hargreaves]

Hi I am trying to code my conditional formatting as I have more than 3
clauses to use.

I cant seem to get the code to work no matter what I name the sub. What do
I need to name it?

Select Case ActiveCell.Value
Case > namedrange1 And < namedrange2
Selection.Interior.ColorIndex = 6
Case >namedrange2 And <namedrange 3
Selection.Interior.ColorIndex = 5
End Select

If I need to write some more code what would it include?

Thanks for your help.
Rob

 

[Nick Hebb]

Are you using Conditional Formatting (i.e. Format > Conditional
Formatting ... from the menu) or are you trying to code it into a sub?

 

[Robert Hargreaves]

I am trying to code it.

I have 4 clauses and want to use a sheet with cells as named ranges to avoid
the menus in conditional formatting

Thanks for any suggestions you might have.
Rob

 

[Nick Hebb]

Have you tried namedrange1.value, namedrange2.value, etc?

 

[Patrick Molloy]

I recently answered a similar question with a full code example. try
searching this NG

 

[Nick Hebb]

You could try something like the following:

Public Sub ConditionalFill()

Dim val As Long
Dim nr1 As Long
Dim nr2 As Long
Dim nr3 As Long

val = ActiveCell.Value
nr1 = Range(ActiveWorkbook.Names("namedrange1")).Value
nr2 = Range(ActiveWorkbook.Names("namedrange2")).Value
nr3 = Range(ActiveWorkbook.Names("namedrange3")).Value

Select Case True
Case val > nr1 And val < nr2
ActiveCell.Interior.ColorIndex = 6
Case val > nr2 And val < nr3
ActiveCell.Interior.ColorIndex = 5
Case Else
ActiveCell.Interior.ColorIndex = xlColorIndexNone
End Select

'If val > nr1 And val < nr2 Then
' ActiveCell.Interior.ColorIndex = 6
'ElseIf val > nr2 And val < nr3 Then
' ActiveCell.Interior.ColorIndex = 5
'Else
' ActiveCell.Interior.ColorIndex = xlColorIndexNone
'End If

End Sub


Personally, I would use the If..ElseIf structure (commented out)
instead of the Select Case. First, it's more intuitive to code, and
second it's reportly faster.

Also, the above code assumes Long for the cell value type - change that
as need be.