Macro help, again!

  • Thread starter Thread starter Rob van Gelder
  • Start date Start date
Hi,

Im continuing on with my "highlighting" macro as per my post yesterday
(thanks for the advice guys), but as always I now want to expand what it
does...unfortunately my code doesnt do what its meant too!

What I would like would be if cell c12 is "ms" then cell h12 would have a
border AND cells h9 and h10 would have a border, have a grey background and
the text in them would become black. When I do this I can get the either
the 1st or the 2nd part working, but not both simultaneously.

Also, is there a way that would mean that i didt have to specify a cell for
the value to be "ms" - im going to be entering data in c11 down to c40 and
would want the macro to highlight the cells in the correspoding row.

Thanks in advance,

Matt
 
Conditional Formatting, although I suspect your formula
may be a tough one given that you're altering columns.

however you could do it using the sheet's Change method.
This fires when a new value is entered into a cell.
Remember that Change is NOT fired if a cell has a
formula, and the value changes as a result of a
recalculation. Conditional Formattting WILL change in
this case.

The following code shows how to test if an entry is made
into column "C" and then I used Select Case to pick row
12 ie C12

I used this since you can easily adapt it for other cells
in C as you indicate. However, you don't give any
algorithm to indicate how you'd select the other cells to
be formatted.

Paste the followign code into the sheet's code page. to
get to the code page quickly, just right-click the sheet
tab & select View Code,

Option Explicit

Private Sub Worksheet_Change(ByVal target As Range)
If target.Column = 3 Then
If target.Value = "ms" Then
Select Case target.Row
Case 12
setCell Range("H12,H10,H9")
Case Else
End Select
End If

End If

End Sub

Private Sub setCell(target As Range)

With target
.Borders(xlDiagonalDown).LineStyle = xlNone
.Borders(xlDiagonalUp).LineStyle = xlNone
With .Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
With .Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlMedium
.ColorIndex = xlAutomatic
End With
.Font.ColorIndex = 1
With .Interior
.ColorIndex = 15
.Pattern = xlSolid
End With
End With


End Sub

Patrick Molloy
Microsoft Excel MVP
 
Back
Top