Macro help, again!
Matt,
Sounds like a job for Conditional Formatting. Rob "Matt B" wrote in message ... 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 |
Macro help, again!
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 |
Macro help, again!
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 -----Original Message----- 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 . |
All times are GMT +1. The time now is 10:44 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com