# When I have a Tie and the same number is referenced is there a way to change the color to RED?

#### RudeBoy

##### Active Member
When I have a Tie and the same number is referenced is there a way to change the color to RED?
See below an example we had a tie 2477 sometimes we have multiple ties so wanted to know if I have a tie how can I have the tie number be turned to RED.

Example so If I have three people tied at 2477 then I would want to turn those ties number 2477 turned to RED. Thanks for your help

 TEAM EVENT 20th Place TEAM EVENT 20th Place I GOT ONE 2477​ Ain't Got No Haters 2477​

#### kweaver

##### Well-known Member
Yes, it's a workbook open event. Details can be read here to implement it for yourself:

Workbook Open Event

### Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

#### RudeBoy

##### Active Member
Yes, it's a workbook open event. Details can be read here to implement it for yourself:

Workbook Open Event
I read that & built this - but it still does not work on entering the Work Book

What did I code wrong?
Sorry for so many questions - Thanks for your help

Private Sub Workbook_Open()

Dim lr As Long, i As Long
lr = Cells(Rows.Count, "A").End(xlUp).Row
lr = lr - 2
For i = 4 To lr Step 6
If Cells(i, 3) = Cells(i, 7) And Cells(i, 3) = Cells(i, 11) Then
Cells(i, 3).Interior.ColorIndex = 3
Cells(i, 7).Interior.ColorIndex = 3
Cells(i, 11).Interior.ColorIndex = 3
ElseIf Cells(i, 3) = Cells(i, 7) Then
Cells(i, 3).Interior.ColorIndex = 3
Cells(i, 7).Interior.ColorIndex = 3
ElseIf Cells(i, 3) = Cells(i, 11) Then
Cells(i, 3).Interior.ColorIndex = 3
Cells(i, 11).Interior.ColorIndex = 3
ElseIf Cells(i, 7) = Cells(i, 11) Then
Cells(i, 7).Interior.ColorIndex = 3
Cells(i, 11).Interior.ColorIndex = 3
End If
Next i
End Sub

#### kweaver

##### Well-known Member
Did you put that in "ThisWorkbook" ?

If you have other sheets in your workbook, insert a line after the DIM line that is: Worksheets("the_name_of_this_sheet").Activate

For example, I have that data on Sheet7, so my line looks like: Worksheets('Sheet7").Activate

#### RudeBoy

##### Active Member
Did you put that in "ThisWorkbook" ?

If you have other sheets in your workbook, insert a line after the DIM line that is: Worksheets("the_name_of_this_sheet").Activate

For example, I have that data on Sheet7, so my line looks like: Worksheets('Sheet7").Activate
I don't seem to get it - here is a picture of what my window looks like - Hope this helps to get me going Thanks so much

#### Attachments

• Auto Run.JPG
144.7 KB · Views: 2

#### kweaver

##### Well-known Member

Your picture doesn't have the workbook open at the top. AND, it should be in ThisWorkbook and NOT one of the sheets.

Code:
``````Private Sub Workbook_Open()
Dim lr As Long, i As Long, clr As Long
clr = 6
Worksheets("Sheet7").Activate  ' put YOUR sheet name in the double quotes
lr = Cells(Rows.Count, "A").End(xlUp).Row
lr = lr - 2
For i = 4 To lr Step 6
If Cells(i, 3) = Cells(i, 7) And Cells(i, 3) = Cells(i, 11) Then
Cells(i, 3).Interior.ColorIndex = clr
Cells(i, 7).Interior.ColorIndex = clr
Cells(i, 11).Interior.ColorIndex = clr
ElseIf Cells(i, 3) = Cells(i, 7) Then
Cells(i, 3).Interior.ColorIndex = clr
Cells(i, 7).Interior.ColorIndex = clr
ElseIf Cells(i, 3) = Cells(i, 11) Then
Cells(i, 3).Interior.ColorIndex = clr
Cells(i, 11).Interior.ColorIndex = clr
ElseIf Cells(i, 7) = Cells(i, 11) Then
Cells(i, 7).Interior.ColorIndex = clr
Cells(i, 11).Interior.ColorIndex = clr
End If
Next i
End Sub``````

Last edited:

#### RudeBoy

##### Active Member
Your picture doesn't have the workbook open at the top. AND, it should be in ThisWorkbook and NOT one of the sheets.

Code:
``````Private Sub Workbook_Open()
Dim lr As Long, i As Long, clr As Long
clr = 6
Worksheets("Sheet7").Activate  ' put YOUR sheet name in the double quotes
lr = Cells(Rows.Count, "A").End(xlUp).Row
lr = lr - 2
For i = 4 To lr Step 6
If Cells(i, 3) = Cells(i, 7) And Cells(i, 3) = Cells(i, 11) Then
Cells(i, 3).Interior.ColorIndex = clr
Cells(i, 7).Interior.ColorIndex = clr
Cells(i, 11).Interior.ColorIndex = clr
ElseIf Cells(i, 3) = Cells(i, 7) Then
Cells(i, 3).Interior.ColorIndex = clr
Cells(i, 7).Interior.ColorIndex = clr
ElseIf Cells(i, 3) = Cells(i, 11) Then
Cells(i, 3).Interior.ColorIndex = clr
Cells(i, 11).Interior.ColorIndex = clr
ElseIf Cells(i, 7) = Cells(i, 11) Then
Cells(i, 7).Interior.ColorIndex = clr
Cells(i, 11).Interior.ColorIndex = clr
End If
Next i
End Sub``````
I am getting an error now

I uploaded a picture of the VBA screen

#### Attachments

• pic.JPG
186.1 KB · Views: 2

#### kweaver

##### Well-known Member

Well, what is the error? I just noticed in your picture that the macro is in a module. It cannot be there as I've said a few times.
It needs to be in ThisWorkbook. Your picture shows it's in Module 5.

#### RudeBoy

##### Active Member
Well, what is the error? I just noticed in your picture that the macro is in a module. It cannot be there as I've said a few times.
It needs to be in ThisWorkbook. Your picture shows it's in Module 5.

View attachment 37708
Ok I got it in ThisWorkbook but I do not see it activating when I enter that sheet?
Here is the latest picture

#### Attachments

• pic1.JPG
183.6 KB · Views: 3

#### kweaver

##### Well-known Member
Unfortunately, I am not sure why. Works without a hitch for me.

#### RudeBoy

##### Active Member
Unfortunately, I am not sure why. Works without a hitch for me.
I do have other sheets auto sorting using the below code when I enter those sheets:
Let me know if there is another way to get this going thanks for your help - stay safe
Sub Worksheet_Activate()
'
' RnkSrt Macro
' Modified on 04/17/2003 by Rude Boy Chris
'
rowcount = ActiveSheet.UsedRange.Rows.Count
Range(Cells(9, 1), Cells(rowcount, 13)).Select

Selection.Sort Key1:=Range("F9"), Order1:=xlAscending, _
Orientation:=xlTopToBottom
Range("A1").Activate
End Sub

Replies
3
Views
153
Replies
3
Views
666
Replies
1
Views
166
Replies
2
Views
57
Replies
11
Views
284

1,129,472
Messages
5,636,516
Members
416,922
Latest member
defectexpress

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back