Conditional formatting code wont work

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

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

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" <[email protected]> wrote in message
news:[email protected]...
> Are you using Conditional Formatting (i.e. Format > Conditional
> Formatting ... from the menu) or are you trying to code it into a sub?
>
>

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

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

"Robert Hargreaves" <nrgsav@REM_Thistiscali.co.uk> wrote in message
news:[email protected]...
> 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
>
>

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.