Format Row Based on Cell Value Selection

[Mike]

Try to figure out how to write a macro button to color all rows "yellow"
that match a selected cell's value in 2 ranges.

Specifically, I want to be able to select a cell in column A from sheet1,
click a macro button, and highlight every row in 2 ranges "Players1" and
"Players2" that contains the value from the selected cell in the first
column of either range.

Also, wondering how to undo

Thanks

 

[Guest]

Hi Mike,

How are the ranges named? Are they named in the interactive mode with Define
Name or are they named in a macro with Set rnge = range etc?

Can you provide an example of both the named ranges?

Are both ranges and the selected cell to match on the same worksheet? If on
multiple worksheets then what ranges are on each sheet?

Also, does the match have to be in both ranges on the same row. That is are
the ranges side by side and two matches required or do you mean if the match
is found in either one range or the other range then color the row.

If I interpret your request correctly, then only need to look in the first
column of each range. Please confirm if this is correct.

Regards,

OssieMac

 

[Mike]

Ranges are defined with interactive mode.

Ranges are on different worksheets than the selected cell value

Matches are separate to each range. So I would expect to highlight one row
in each range if there is a match.

Ranges:
"Player1" on Sheet2
"Player2" on Sheet3

Sample Range:
PlayerID Name Points
Bill001 Bill Jones 32
Joe001 Joe Smith 43

My selected cell would contain the value "Joe001" and I would expect to
hightlight all rows with Joe001 in my ranges. Thanks

 

[Guest]

I should have said "are all names found in first column" (Not first cell like
I wrote.) On a second look at your reply, your example does show them in the
first column so the macro should work fine.

Regards again,

OssieMac

 

[Guest]

Hi Mike,

Unfortunately you did not confirm the last thing on my previous post. Are
all the names to be found in the first cell of each of the ranges Players1
and Players2.

Here is what I have assuming the above is correct. It is full of comment so
that you can see what is happening.

If you don't want to 'undo' the colors each time you run the macro because
you want to search more than one then you will see that I have said where to
remove the lines.

You can undo by simply selecting all cells in the worksheet and using the
fill icon on the toolbar and select the appropriate no fill option.

If you need any more from me than I need a quick answer from you because
I'll be unavailable for a few days.


Sub Color_Rows()

Dim rng1 As Range 'For Players1 range
Dim rng2 As Range 'For Players2 range
Dim cellSelect As Variant 'For Selected cell
Dim i As Single 'Used in For/Next loops

'Test if more than one cell selected.
'This must be the first test otherwise a
'VBA code error will occur on next test.
If Selection.Cells.Count > 1 Then
GoTo invalSelect 'Selection was invalid
End If

'Test if selection is a blank cell or
'if selection not in column A.
If Selection = "" Or Selection.Column <> 1 Then
GoTo invalSelect 'Selection was invalid
End If

'Remove any existing interior colors.
'If you don't want to do this every time then the next 2
'lines need to be in a separate procedure.
Sheets("Sheet2").Cells.Interior.ColorIndex = xlColorIndexNone
Sheets("Sheet3").Cells.Interior.ColorIndex = xlColorIndexNone

'Assign ranges to VBA variables
Set rng1 = Sheets("Sheet2").Range("Players1")

Set rng2 = Sheets("Sheet3").Range("Players2")

'Assign Activecell value to a VBA variable
cellSelect = ActiveCell

With rng1
For i = 1 To .Rows.Count 'To number of rows in rng1
If .Cells(i, 1) = cellSelect Then 'Test cells column 1 only
.Cells(i, 1).EntireRow.Interior.ColorIndex = 6
End If
Next i
End With

With rng2
For i = 1 To .Rows.Count 'To number of rows in rng2
If .Cells(i, 1) = cellSelect Then 'Test cells column 1 only
.Cells(i, 1).EntireRow.Interior.ColorIndex = 6
End If
Next i
End With

End 'If get to here then end and do not process InvalSelect code

invalSelect: 'Label for start of invalid selection routine

MsgBox "Invalid selection. Rules for selection are:-" & _
Chr(13) & Chr(13) & "Select one cell only" & _
Chr(13) & "Selection to be in column A only" & _
Chr(13) & "Selection not to be a blank cell" & _
Chr(13) & Chr(13) & "Processing will terminate"

End Sub

Regards,

OssieMac