Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
using a cell value to control a counter inside a macro and displaying macro value | Excel Worksheet Functions | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |