Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Cell Highlighting -- Tricky
I am attempting to highlight four cells in a
particular row based upon which dealer is chosen by a user. For the sake of simplicity here is a sample table of similar data that we are using: Rank Dealer State % of Total 1 Acme CO 12.5 2 ABC KS 11.7 3 Wildcat Paint MO 9.4 4 Harris Co. FL 7.1 While I can highlight the dealer name fairly easy, I'm having trouble having the rank highlight based on if the dealer is highlighted. For example, if the dealer name "Acme" was in cell E56 and was highlighted, then I'd like to also have the rank "1" in cell D56 also highlight based on the fact that E56 was highlighted. If I can figure out how to highlight the rank, then that will make doing the state and % of total very easy to complete. Here is the "rookie" code that I'm using to highlight the dealer name however, I can't get the rank to highlight. Thanks in advance for your help! James --------------------------------------------------- Private Sub Worksheet_Activate() 'Highlight active dealer in the ranked dealer list. Dim cel1 As Range Dim cel2 As Range Dim wsSource As Worksheet Dim strDealer As String Set wsSource = Sheets("Dealer") strDealer = wsSource.Range("C2") 'This block will highlight the dealer name For Each cel1 In Range("E56:E70").Cells If cel1.Value = strDealer Then cel1.Interior.ColorIndex = 44 cel1.Font.FontStyle = "Bold" Else cel1.Interior.ColorIndex = 0 cel1.Font.FontStyle = "Regular" End If Next 'This block will hightlight the rank, one 'cell to the left of the dealer name, if 'the dealer name is highlighted. For Each cel2 In Range("D56:D70").Cells For Each cel1 In Range("E56:E70").Cells If cel1.Value = strDealer Then cel2.Interior.ColorIndex = 44 cel2.Font.FontStyle = "Bold" Else cel2.Interior.ColorIndex = 0 cel2.Font.FontStyle = "Regular" End If Next Next End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Cell Highlighting -- Tricky
one could do this with code, but you could also use Conditional
Formatting: Select D56:E70, with D56 active, then choose Format/Conditional Formatting and enter: CF1: =$E56=Dealer!$C$2 Format: <pattern/color44, <font/<bold In article , James wrote: I am attempting to highlight four cells in a particular row based upon which dealer is chosen by a user. For the sake of simplicity here is a sample table of similar data that we are using: Rank Dealer State % of Total 1 Acme CO 12.5 2 ABC KS 11.7 3 Wildcat Paint MO 9.4 4 Harris Co. FL 7.1 While I can highlight the dealer name fairly easy, I'm having trouble having the rank highlight based on if the dealer is highlighted. For example, if the dealer name "Acme" was in cell E56 and was highlighted, then I'd like to also have the rank "1" in cell D56 also highlight based on the fact that E56 was highlighted. If I can figure out how to highlight the rank, then that will make doing the state and % of total very easy to complete. Here is the "rookie" code that I'm using to highlight the dealer name however, I can't get the rank to highlight. Thanks in advance for your help! James --------------------------------------------------- Private Sub Worksheet_Activate() 'Highlight active dealer in the ranked dealer list. Dim cel1 As Range Dim cel2 As Range Dim wsSource As Worksheet Dim strDealer As String Set wsSource = Sheets("Dealer") strDealer = wsSource.Range("C2") 'This block will highlight the dealer name For Each cel1 In Range("E56:E70").Cells If cel1.Value = strDealer Then cel1.Interior.ColorIndex = 44 cel1.Font.FontStyle = "Bold" Else cel1.Interior.ColorIndex = 0 cel1.Font.FontStyle = "Regular" End If Next 'This block will hightlight the rank, one 'cell to the left of the dealer name, if 'the dealer name is highlighted. For Each cel2 In Range("D56:D70").Cells For Each cel1 In Range("E56:E70").Cells If cel1.Value = strDealer Then cel2.Interior.ColorIndex = 44 cel2.Font.FontStyle = "Bold" Else cel2.Interior.ColorIndex = 0 cel2.Font.FontStyle = "Regular" End If Next Next End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Cell Highlighting -- Tricky
Private Sub Worksheet_Activate()
'Highlight active dealer in the ranked dealer list. Dim cel1 As Range Dim cel2 As Range Dim wsSource As Worksheet Dim strDealer As String Set wsSource = Sheets("Dealer") strDealer = wsSource.Range("C2") 'This block will highlight the dealer name For Each cel1 In Range("E56:E70").Cells If cel1.Value = strDealer Then With cel1.offset(0,-1).Resize(1,4) .Interior.ColorIndex = 44 .Font.FontStyle = "Bold" End With Else With cel1.offst(0,-1).Resize(1,4) .Interior.ColorIndex = 0 .Font.FontStyle = "Regular" End With End If Next End Sub -- Regards, Tom Ogilvy "James" wrote: I am attempting to highlight four cells in a particular row based upon which dealer is chosen by a user. For the sake of simplicity here is a sample table of similar data that we are using: Rank Dealer State % of Total 1 Acme CO 12.5 2 ABC KS 11.7 3 Wildcat Paint MO 9.4 4 Harris Co. FL 7.1 While I can highlight the dealer name fairly easy, I'm having trouble having the rank highlight based on if the dealer is highlighted. For example, if the dealer name "Acme" was in cell E56 and was highlighted, then I'd like to also have the rank "1" in cell D56 also highlight based on the fact that E56 was highlighted. If I can figure out how to highlight the rank, then that will make doing the state and % of total very easy to complete. Here is the "rookie" code that I'm using to highlight the dealer name however, I can't get the rank to highlight. Thanks in advance for your help! James --------------------------------------------------- Private Sub Worksheet_Activate() 'Highlight active dealer in the ranked dealer list. Dim cel1 As Range Dim cel2 As Range Dim wsSource As Worksheet Dim strDealer As String Set wsSource = Sheets("Dealer") strDealer = wsSource.Range("C2") 'This block will highlight the dealer name For Each cel1 In Range("E56:E70").Cells If cel1.Value = strDealer Then cel1.Interior.ColorIndex = 44 cel1.Font.FontStyle = "Bold" Else cel1.Interior.ColorIndex = 0 cel1.Font.FontStyle = "Regular" End If Next 'This block will hightlight the rank, one 'cell to the left of the dealer name, if 'the dealer name is highlighted. For Each cel2 In Range("D56:D70").Cells For Each cel1 In Range("E56:E70").Cells If cel1.Value = strDealer Then cel2.Interior.ColorIndex = 44 cel2.Font.FontStyle = "Bold" Else cel2.Interior.ColorIndex = 0 cel2.Font.FontStyle = "Regular" End If Next Next End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Tricky Tricky episode 2!!! | Excel Programming | |||
Tricky Tricky episode 2!!! | Excel Programming | |||
Formula to show zero if reference cell is zero? tricky! | Excel Worksheet Functions | |||
Highlighting blanks via GO TO SPECIAL is not highlighting blank cells - HELP, I'm totally stuck. | Excel Discussion (Misc queries) | |||
This one is tricky....Macro to save file as cell value x in di | Excel Discussion (Misc queries) |