raphagcwill
New Member
- Joined
- Jan 12, 2016
- Messages
- 41
Hi there,
wondering if you could help me to make the below.
I have a workbook with more then 20 sheets. My goal is to make each tabs change colour if a specific text is found on column N
I have managed to write the below code, but it takes 10 seconds for it to complete the task. It is worth saying that I am not very familiar with VBA and that the below was the result of me combining solutions i found on the internet loops - Excel VBA; If a value in column M equals x then column A should Equal Column E - Stack Overflow
Can somebody help me to make it faster? Should I insert a lastrow on my code?
Thanks in advance
Sub CHANGE_TAB_COLOUR()
Dim SOURCEWS As Worksheet
Set SOURCEWS = ActiveSheet
Dim lngLoop As Long
Dim LastRow As Long
lngLoop = 1
For lngLoop = 1 To Rows.Count
If Cells(lngLoop, 14).Value = "SETTLE" Then
With SOURCEWS.Tab
.Color = 5296274
.TintAndShade = 0
End With
ElseIf Cells(lngLoop, 14).Value = "PPO" Then
With SOURCEWS.Tab
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.149998474074526
End With
ElseIf Cells(lngLoop, 14) = "PO" Then
With SOURCEWS.Tab
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.149998474074526
End With
End If
Next
End Sub
wondering if you could help me to make the below.
I have a workbook with more then 20 sheets. My goal is to make each tabs change colour if a specific text is found on column N
I have managed to write the below code, but it takes 10 seconds for it to complete the task. It is worth saying that I am not very familiar with VBA and that the below was the result of me combining solutions i found on the internet loops - Excel VBA; If a value in column M equals x then column A should Equal Column E - Stack Overflow
Can somebody help me to make it faster? Should I insert a lastrow on my code?
Thanks in advance
Sub CHANGE_TAB_COLOUR()
Dim SOURCEWS As Worksheet
Set SOURCEWS = ActiveSheet
Dim lngLoop As Long
Dim LastRow As Long
lngLoop = 1
For lngLoop = 1 To Rows.Count
If Cells(lngLoop, 14).Value = "SETTLE" Then
With SOURCEWS.Tab
.Color = 5296274
.TintAndShade = 0
End With
ElseIf Cells(lngLoop, 14).Value = "PPO" Then
With SOURCEWS.Tab
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.149998474074526
End With
ElseIf Cells(lngLoop, 14) = "PO" Then
With SOURCEWS.Tab
.ThemeColor = xlThemeColorDark1
.TintAndShade = -0.149998474074526
End With
End If
Next
End Sub