Highlighting Range of Cells based on another cell with 5 condition

[Guest]

Hi, I'm pretty new with VBA. I'm trying to highlight a range of cells for
example (A10:N500) based on cells located in column M. If M10=CA Then
A10:N10 will be highlighted yellow Else If M11=GI Then A11:N11 will be
highlighted orange and about three more coniditons. I would appreciate a
code for this.

-Thank you

 

[Guest]

Hi Alex

Try using conditioal formating to achieve your goal I think this will be
easier than using VBA.

 

[Bob Phillips]

If M10 were GI, would A10:N10 go orange as well?

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

There are 5 conditions. The conditional formatting only allows three
condition.

 

[Guest]

Yes. depending what M is would highlight the row accordingly.

 

[Bob Phillips]

'-----------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
'-----------------------------------------------------------------
Const WS_RANGE As String = "M10:M500"

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 "GI": .Offset(0,-12).Resize(,12).Interior.ColorIndex =
46 'orange
Case "CA": .Offset(0,-12).Resize(,12).Interior.ColorIndex =
6 'yellow
'etc.
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.


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

How would I use this as a macro. So when I hit Ctrl+D the code would be run.
Also the criteria is a formula, which the code doesn't work. For example
GI, HO are calculated through a formula.