Macro Excel Change color based on referenced cell

[mahiney8184]

I found this place that has a macro button that does something similar to what I want to do. I am able to adjust everything to suite my file perfectly so far except one thing. I am trying to get the color change to be based off of another cell, not a set value.
http://www.techonthenet.com/excel/macros/change_color.php

Based on the model that this person built... an example of what I Want to do is where it says "Case "030087"" in the code, I want it to reference Cell M14. So IF the cell the function is testing is = to cell M14, color these rows.

I know some C++ and HTML, but NO Macro. I am trying to learn, so please explain.

I tried these and they didn't work.

Case "M14"
Case Is = "M14"
Case Is = M14

Please help! Thank you.

 

[lenze]

Hi and welcome to the board

Case = Range("$M$14")

That said, post your code. The code you linked to may be more than you need!!

lenze

 

[mahiney8184]

I tried that and when I click off of it, it changes to:

Case Is = Range("$M$14")
Any advice on this would be greatly appreciated.

Thanks!

 

[lenze]

The VBE is just making up for my laziness!!

Case Is = Range("$M$14")

is the correct syntax.
lenze

 

[mahiney8184]

I'd love to attach the excel file, but I think you can find it in the link above. With this code below, it changes the cells green, but does not change cells blue at are equal to M14.

Well here is what I have for the code....

Sub sort()
'
' Keyboard Shortcut: Ctrl+s
'
ActiveWindow.LargeScroll Down:=1
Range("A32").Select
ActiveWindow.LargeScroll Down:=1
Range("A65").Select
ActiveWindow.LargeScroll Down:=1
Range("A98").Select
ActiveWindow.LargeScroll Down:=1
Range("A131").Select
ActiveWindow.LargeScroll Down:=1
Range("A164").Select
ActiveWindow.LargeScroll Down:=1
Range("A197").Select
ActiveWindow.LargeScroll Down:=1
Range("A230").Select
ActiveWindow.LargeScroll Down:=1
Range("C603:L1193").Select
Selection.sort Key1:=Range("E603"), Order1:=xlDescending, Header:=xlGuess _
, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
Range("A1").Select
End Sub

Sub Update_Row_Colors()
Dim LRow As Integer
Dim LCell As String
Dim LColorCells As String
'Start at row 7
LRow = 7

'Update row colors for the first 2000 rows
While LRow < 2000
LCell = "C" & LRow
'Color will changed in columns A to K
LColorCells = "A" & LRow & ":" & "K" & LRow

Select Case Left(Range(LCell).Value, 6)

'Set row color to light blue
Case Is = Range("$M$14")
Range(LColorCells).Interior.ColorIndex = 34
Range(LColorCells).Interior.Pattern = xlSolid

'Set row color to light green
Case "030087"
Rows(LRow & ":" & LRow).Select
Range(LColorCells).Interior.ColorIndex = 35
Range(LColorCells).Interior.Pattern = xlSolid

'Set row color to light yellow
'Case "063599"
' Rows(LRow & ":" & LRow).Select
' Range(LColorCells).Interior.ColorIndex = 19
' Range(LColorCells).Interior.Pattern = xlSolid

'Default all other rows to no color
Case Else
'Rows(LRow & ":" & LRow).Select
'Range(LColorCells).Interior.ColorIndex = xlNone

End Select

LRow = LRow + 1
Wend


Range("A1").Select


End Sub

 

[lenze]

Are your cells and M14 formatted the same?? Either Text of Number?

I have no idea what you are trying to do with your Sort sub!! Why all the selections and Scrolls when you don't use them??
lenze