Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pass condition formatting across worksheets via vba.
Hi guy's
Currently, I have this working very well. .................................................. .. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Row < 5 Then Exit Sub On Error GoTo ErrHandler Application.EnableEvents = False If Target.Column = 11 Then If IsNumeric(Target.Value) Then With Target .Offset(, -10).Resize(, 14).Interior.ColorIndex = 6 End With End If If Target.Value = "" Then With Target .Offset(, -10).Resize(, 14).Interior.ColorIndex = 2 End With End If If Target.Column = 12 Then If IsNumeric(Target.Value) Then With Target .Offset(, -11).Resize(, 14).Interior.ColorIndex = 6 End With End If If Target.Value = "" Then With Target .Offset(, -11).Resize(, 14).Interior.ColorIndex = 2 End With End If End If End If ErrHandler: Application.EnableEvents = True End Sub .................................................. .. What I have been asked to do is expand each of the major IF's and branch it across 2 other worksheets beyond this sheet("Inbound")and by I mean the following: The 1st Part: in the first Target.column which = 11, I need to .Offset(,9).value and use that value to cycle through 2 others, Firstly: sheets("Outbound").Columns("E:E")= find.("Inbound").Target.Offset(,9).Value and change the interior color of Column("E").offset(,-4).Resize(,13).Interior.ColorIndex to match. The 2nd Part: For this part we have to look at a second matching criteria to locate the Target.Value. Using our established Sheets("Outbound").Column.Value match in Column("E") we need to obtain our second criteria value, so it would be: Sheets("Outbound").Columns("E").value And Columns("E").Offset(,8).value So the final part of this rather complicated looking code will be Sheets("" & Columns("E").Offset(,8).value).Activate Columns("G:G").Target.Value.Offset(,-6).resize(,12).Interior.ColorIndex to match. So this long-winded mess should look something like this in the 1st If section: If Target.Column = 11 Then If IsNumeric(Target.Value) Then With Target ..Offset(, -10).Resize(, 14).Interior.ColorIndex = 6 End With sheets("Outbound").Columns("E:E")= find.("Inbound").Target.Offset(,9).Value If Find = True Then Column("E").offset(,-4).Resize(,13).Interior.ColorIndex to match Sheets("" & Columns("E").Offset(,8).value).Activate Columns("G:G").Target.Value.Offset(,-6).resize(,12).Interior.ColorIndex to match. End If I truly hope someone can make sense of what it is I, or should I say somebody else actually needs. A Massive TIA Cheers Mick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating a pass of fail condition | New Users to Excel | |||
Conditional Formatting - Getting pass the 3 condition limit | Excel Discussion (Misc queries) | |||
Conditional Formatting - Getting pass the 3 condition limit | Excel Discussion (Misc queries) | |||
Pass array of worksheets to ActiveX DLL (VB6) | Excel Programming | |||
How do I pass a worksheets name to function? | Excel Programming |