Need 6 Conditions / Conditional Formatting

[Rothman]

I have a worksheet with six numbers in cells B61:G61.

I then have all sorts of numbers from A6:G58.

I just want the numbers in A6:G58 that match my six numbers in B61:G61 to be
highlighted in yellow.

I've come across the below code on here, but my knowledge of VBA is close to
nil. Is there anyway of changing this code so Excel does what I want it to
do?

Thanks yet again!

The code:

"Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Select Case .Value
Case 1: .Interior.ColorIndex = 3 'red
Case 2: .Interior.ColorIndex = 6 'yellow
Case 3: .Interior.ColorIndex = 5 'blue
Case 4: .Interior.ColorIndex = 10 'green
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub"

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.

[bpeltzer]

The number that matters is how many different formats you need presented.
That's just one as you've described it. So you just need a formula that will
return true if the cell in question matches one of your six values.
Select the first cell you want to conditionally format, say A6, then Format
> Conditional Formatting. Set the drop-down to 'is formula' and enter the
formula =not(isna(match(a6,$B$61:$G$61,false))), then set the appropriate
format (a fill pattern of yellow).
You should be able to copy/paste that format to the rest of the cells to
which this CF applies.
--Bruce

"Rothman" wrote:

> I have a worksheet with six numbers in cells B61:G61.
>
> I then have all sorts of numbers from A6:G58.
>
> I just want the numbers in A6:G58 that match my six numbers in B61:G61 to be
> highlighted in yellow.
>
> I've come across the below code on here, but my knowledge of VBA is close to
> nil. Is there anyway of changing this code so Excel does what I want it to
> do?
>
> Thanks yet again!
>
> The code:
>
> "Private Sub Worksheet_Change(ByVal Target As Range)
> Const WS_RANGE As String = "H1:H10"
>
> On Error GoTo ws_exit:
> Application.EnableEvents = False
> If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
> With Target
> Select Case .Value
> Case 1: .Interior.ColorIndex = 3 'red
> Case 2: .Interior.ColorIndex = 6 'yellow
> Case 3: .Interior.ColorIndex = 5 'blue
> Case 4: .Interior.ColorIndex = 10 'green
> End Select
> End With
> End If
>
> ws_exit:
> Application.EnableEvents = True
> End Sub"
>
> 'This is worksheet event code, which means that it needs to be
> 'placed in the appropriate worksheet code module, not a standard
> 'code module. To do this, right-click on the sheet tab, select
> 'the View Code option from the menu, and paste the code in.
>

[davesexcel]

Getting past Conditional Formattings 3 Criteria Limit

This code must be placed in the Private Module of the Worksheet. To get there right click on the sheet name tab and select "View Code".

Excel's very handy Conditional Formatting unfortunately only allows up to 3 conditions. The method below gets around this limit. It is set to work on A1:A10 only. event.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim icolor As Integer
If Not Intersect(Target, Range("A1:A10")) is Nothing Then
Select Case Target
Case 1 To 5
icolor = 6
Case 6 To 10
icolor = 12
Case 11 To 15
icolor = 7
Case 16 To 20
icolor = 53
Case 21 To 25
icolor = 15
Case 26 To 30
icolor = 42
Case Else
'Whatever
End Select
Target.Interior.ColorIndex = icolor
End If

End Sub

[Ron Rosenfeld]

On Tue, 14 Mar 2006 09:50:29 -0800, Rothman <[email protected]>
wrote:

>I have a worksheet with six numbers in cells B61:G61.
>
>I then have all sorts of numbers from A6:G58.
>
>I just want the numbers in A6:G58 that match my six numbers in B61:G61 to be
>highlighted in yellow.
>
>I've come across the below code on here, but my knowledge of VBA is close to
>nil. Is there anyway of changing this code so Excel does what I want it to
>do?
>
>Thanks yet again!
>
>The code:
>
>"Private Sub Worksheet_Change(ByVal Target As Range)
>Const WS_RANGE As String = "H1:H10"
>
> On Error GoTo ws_exit:
> Application.EnableEvents = False
> If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
> With Target
> Select Case .Value
> Case 1: .Interior.ColorIndex = 3 'red
> Case 2: .Interior.ColorIndex = 6 'yellow
> Case 3: .Interior.ColorIndex = 5 'blue
> Case 4: .Interior.ColorIndex = 10 'green
> End Select
> End With
> End If
>
>ws_exit:
> Application.EnableEvents = True
>End Sub"
>
>'This is worksheet event code, which means that it needs to be
>'placed in the appropriate worksheet code module, not a standard
>'code module. To do this, right-click on the sheet tab, select
>'the View Code option from the menu, and paste the code in.

You only have one condition to match, not six. So just use the regular
conditional formatting routines.

Highlight A6:G58 (A6 should then be the active cell)

Format/Conditional Formatting
Formula Is:

=COUNTIF($B$61:$G$61,A6)>0

Format/Patterns and select Yellow
OK
OK


--ron

[Rothman]

Ah, so that's what those drop down menus are for!

I need a vacation.

Thank you very much.

"bpeltzer" wrote:

> The number that matters is how many different formats you need presented.
> That's just one as you've described it. So you just need a formula that will
> return true if the cell in question matches one of your six values.
> Select the first cell you want to conditionally format, say A6, then Format
> > Conditional Formatting. Set the drop-down to 'is formula' and enter the
> formula =not(isna(match(a6,$B$61:$G$61,false))), then set the appropriate
> format (a fill pattern of yellow).
> You should be able to copy/paste that format to the rest of the cells to
> which this CF applies.
> --Bruce
>
> "Rothman" wrote:
>
> > I have a worksheet with six numbers in cells B61:G61.
> >
> > I then have all sorts of numbers from A6:G58.
> >
> > I just want the numbers in A6:G58 that match my six numbers in B61:G61 to be
> > highlighted in yellow.
> >
> > I've come across the below code on here, but my knowledge of VBA is close to
> > nil. Is there anyway of changing this code so Excel does what I want it to
> > do?
> >
> > Thanks yet again!
> >
> > The code:
> >
> > "Private Sub Worksheet_Change(ByVal Target As Range)
> > Const WS_RANGE As String = "H1:H10"
> >
> > On Error GoTo ws_exit:
> > Application.EnableEvents = False
> > If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
> > With Target
> > Select Case .Value
> > Case 1: .Interior.ColorIndex = 3 'red
> > Case 2: .Interior.ColorIndex = 6 'yellow
> > Case 3: .Interior.ColorIndex = 5 'blue
> > Case 4: .Interior.ColorIndex = 10 'green
> > End Select
> > End With
> > End If
> >
> > ws_exit:
> > Application.EnableEvents = True
> > End Sub"
> >
> > 'This is worksheet event code, which means that it needs to be
> > 'placed in the appropriate worksheet code module, not a standard
> > 'code module. To do this, right-click on the sheet tab, select
> > 'the View Code option from the menu, and paste the code in.
> >