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

RudeBoy

Active Member
Joined
Feb 2, 2003
Messages
364
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 EVENT20th PlaceTEAM EVENT20th Place
I GOT ONE
2477​
Ain't Got No Haters
2477​
 

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
Joined
Feb 2, 2003
Messages
364
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
Joined
May 8, 2018
Messages
1,732
Office Version
  1. 365
  2. 2010
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
Joined
Feb 2, 2003
Messages
364
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
    Auto Run.JPG
    144.7 KB · Views: 2

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,732
Office Version
  1. 365
  2. 2010

ADVERTISEMENT

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
Joined
Feb 2, 2003
Messages
364
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
    pic.JPG
    186.1 KB · Views: 2

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,732
Office Version
  1. 365
  2. 2010

ADVERTISEMENT

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.

1619658076019.png
 

RudeBoy

Active Member
Joined
Feb 2, 2003
Messages
364
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
    pic1.JPG
    183.6 KB · Views: 3

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,732
Office Version
  1. 365
  2. 2010
Unfortunately, I am not sure why. Works without a hitch for me.
 

RudeBoy

Active Member
Joined
Feb 2, 2003
Messages
364
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, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
Range("A1").Activate
End Sub
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top