PDA

View Full Version : Solved: vb conditional formatting



Emoncada
07-07-2010, 08:31 AM
I have a spreadsheet that is updated daily. What I would like is for it to have some vb code in the background so when someone pastes new data it looks at two columns and color format certain matches.

If Column("U") has a value of "Branch" & in that same row Column("M") Left(M***,2)= "NC" then same row range(B:V) highlight blue.

can someone assist with this.

Thanks,

p45cal
07-07-2010, 08:47 AM
You don't need code for this.
As a for instance we'll start with row 6. Select B6:V6 on the sheet.
Go into conditional format and go to the part which uses a formula (FormulaIs: pre xl2007, and Use a formula to determine which cells to format xl2007 onwards) and enter this formula:
=AND(LEFT($M6,2)="NC",$U6="Branch")
choose you format and OK.
Now you can use the format painter to extend this to other rows.

Emoncada
07-07-2010, 08:58 AM
that won't work because the conditional format would be removed when someone pastes a range, unless they use paste special values, but then that would change cell formats they are pasting.

That's the reason I was trying to have this run in the background.

p45cal
07-07-2010, 10:17 AM
right-click the relevant sheet's tab and choose View code, then paste the following at the flashing cursor:
Private Sub Worksheet_Change(ByVal Target As Range)
Set RngToCheck = Intersect(Target.EntireRow, Range("M:M,U:U"), Range("B1:V2000")) 'the last 2000 is to limit how many rows are processed if someone changes a whole column, adjust to suit.
If Not RngToCheck Is Nothing Then
For Each cll In RngToCheck.Areas(1).Cells
Set RngToColour = Range(Cells(cll.Row, "B"), Cells(cll.Row, "V"))
If Cells(cll.Row, "U").Value = "Branch" And Left(Cells(cll.Row, "M").Value, 2) = "NC" Then
RngToColour.Interior.ColorIndex = 37 '23 shades of blue
Else
RngToColour.Interior.ColorIndex = xlNone
End If
Next cll
End If
End Sub
see comment in the code too.

Emoncada
07-07-2010, 10:31 AM
p45cal, that's exactly what I was going for.

Is it possible to have it look at more then just "NC"?
like if "NC" or "SC" etc..

p45cal
07-07-2010, 10:44 AM
untested:Private Sub Worksheet_Change(ByVal Target As Range)
Set RngToCheck = Intersect(Target.EntireRow, Range("M:M,U:U"), Range("B1:V2000")) 'the last 2000 is to limit how many rows are processed if someone changes a whole column, adjust to suit.
If Not RngToCheck Is Nothing Then
For Each cll In RngToCheck.Areas(1).Cells
Set RngToColour = Range(Cells(cll.Row, "B"), Cells(cll.Row, "V"))
Left2 = Left(Cells(cll.Row, "M").Value, 2)
If Cells(cll.Row, "U").Value = "Branch" And (Left2 = "NC" Or Left2 = "SC" Or Left2 = "XX") Then
RngToColour.Interior.ColorIndex = 37 '23 shades of blue
Else
RngToColour.Interior.ColorIndex = xlNone
End If
Next cll
End If
End Sub

Emoncada
07-07-2010, 10:48 AM
Perfect.

Thanks A lot P45cal