Hi all,
I have spent hours yesterday reading and watching youtube around worksheets/ranges and loops and I keep getting stuck and need any assistance if possible.
I have created a list of details of people against a particular team.
There are about 10 rows with about 30 bits of data in each column.
Where there is a specific text for some of the people they are highlighted via conditional formatting.
There are a 6 tabs that have the exact same layout.
In Excel I have this setup working absolutley fine so I have no issues, but realised that my biggest headache at the moment is the conditional formatting, for everytime something new needs to be added in, I need to do this against each of the 10 rows per tab. Therefore I now want to convert this into VBA.
It sounds 'straight forward' but I keep going round in a loop (pun intended ).
What do I need assistance with?
I need to set my ranges for the cells to go to the bottom of the dataset for each tab.
I felt below would be correct, but it appears to not go to the next worksheet.
Also I don't think the Vlookup is being dynamic to look at each row individually.
I guess my 2 questions are:
1) Can you provide some assistance in how to achieve this.
2) Is there any good tutorials I can watch. I keep seeing watching a few but it doesn't appear I can put a ws and range together to loop.
I have spent hours yesterday reading and watching youtube around worksheets/ranges and loops and I keep getting stuck and need any assistance if possible.
I have created a list of details of people against a particular team.
There are about 10 rows with about 30 bits of data in each column.
Where there is a specific text for some of the people they are highlighted via conditional formatting.
There are a 6 tabs that have the exact same layout.
In Excel I have this setup working absolutley fine so I have no issues, but realised that my biggest headache at the moment is the conditional formatting, for everytime something new needs to be added in, I need to do this against each of the 10 rows per tab. Therefore I now want to convert this into VBA.
It sounds 'straight forward' but I keep going round in a loop (pun intended ).
What do I need assistance with?
I need to set my ranges for the cells to go to the bottom of the dataset for each tab.
I felt below would be correct, but it appears to not go to the next worksheet.
Also I don't think the Vlookup is being dynamic to look at each row individually.
I guess my 2 questions are:
1) Can you provide some assistance in how to achieve this.
2) Is there any good tutorials I can watch. I keep seeing watching a few but it doesn't appear I can put a ws and range together to loop.
VBA Code:
Sub formatting()
Dim WrkSht As Worksheet
Set rng1 = Range("D5", "F33")
For Each WrkSht In ActiveWorkbook.Worksheets
With rng1
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, Formula1:="=VLOOKUP(rng1,Data_Details,COLUMNS(DATA!B:G),FALSE)=0"
.Font.Color = vbBlack
.Font.Bold = False
End With
Next WrkSht
End Sub
Last edited by a moderator: