VBA Worksheets and Ranges

[p85ki]

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.

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

 

[Herakles]

You will need to set the rng1 range for each worksheet

Sub formatting()

Dim WrkSht As Worksheet

    For Each WrkSht In ActiveWorkbook.Worksheets

        Set rng1 = WrkSht.Range("D5", "F33")
        
        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

 

[Snakehips]

Similar to above. Plus, if I understand correctly then you are wanting to limit each range according to bottom of data.

See if this helps.

Sub formatting()

Dim WrkSht As Worksheet

'Range Moved *needs to be specified within the For Loop so will be set for each sheet in turn
'Otherwise it will remain set for the Active Sheet only

For Each WrkSht In ActiveWorkbook.Worksheets
'if last row is determinable by data in F or not then

LastRow = WrkSht.Range("F" & Rows.Count).End(xlUp).Row
'* Set range within loop for the current sheet
Set rng1 = WrkSht.Range("D5:F" & LastRow)
With rng1
'.Select   '<<Don't include this line unless you are needing to see each sheet being changed
.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

 

[p85ki]

Thank you so much for that. I wasn't far off! which was great, and annoyingly I was trying different ways for it to work.
I have 14 ranges to do which is the same setup, just different conditions for the conditional formatting.

I have noticed an element that my vlookup is making all of it one colour instead of it meeting the criteria.
Is it becasue it's not looking at each row individually?

Example D5 shows that the value is 1 so it needs to stay whatever colour it is,
D6 has 0 for example, so this needs to be black
D7 has Part Time, my vlookup will have = "Part Time", so if it meets it then it needs to be orange.

Sub formatting() Dim WrkSht As Worksheet For Each WrkSht In ActiveWorkbook.Worksheets Set rng1 = WrkSht.Range("D5", "F33") 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

 

[p85ki]

Thank you snakehips

I love the explanations against it as well. I was partially there, I saw the xlUP and XLDown in my findings but couldn't bring it to life.

The last row is found against multiple rows but 1 of them is F, I will need to create it for each one..

I can see how 'simpler' this is but will look at that once I understand the basics of this

I've just ran your VBA (and also the above) and they both work. One thing I have noted is that the Vlookup is simply not working or is just looking at the top one and making it all red.
Could I be cheeky and ask 2 additional questions:

1) How do I ensure that the vlookup is looking at each row (essentially looking at the name and checking the rota type they are on).
2) I will be using my vlookup to ensure they meet one of the criterias below (there are 16 in total). In excel it's just putting this into quotes, but I can see that the quotes in VBA will close the formula.

 

[Snakehips]

I have to admit that I do not have a clear picture of what you are looking to achieve.
Are you needing to continually edit / update existing conditional formatting rules???
Are you able to download XL2BB from the link above, and post snippets of related data and expected results?

As for including quotes in vba, you need to add additional quotes. So to include the String "Part Time" as a direct string, in a vba expression, it would need to be ""Part Time""

 

[p85ki]

Thank you Snakehips

Are you needing to continually edit / update existing conditional formatting rules???

Yes - I expect once a month (either removing or adding to) but generally it's quite stable.
It's when I do need to add/remove conditional formatting, it's quite alot of work which is where the VBA comes in.

In very simplistic terms, I am just using the VBA to highlight specific people against a manager where they have a specific position type.
If they have none then it's left alone.

Detailed Summary

It's a report that is sent to upper management once a week for them to review the people of where they sit within the business and any concerns.
This is within a group of other spreadsheets but this is the only spreadsheet I now seem to have a struggle with (the rest were done fairly easy).

The report is looking at in total around 400 people that work for us.
This is split up between X managers that have X people.
There is X amount of managers that are sitting in each department which is why there are 6 tabs.

A database is being used to extract all the data a Data tab but I have automated the data to into each of those 6 tabs.
The conditional formatting is looking at each person to see the type of position they are in (full time/part time/weekend only etc...).

Based on that report, it will help review how many people are within each time, hours worked, position type to ensure that we are efficient.

Using excel formulas, it's already working like this. The only headache I've had is the conditional formatting, which I am now trying to put into VBA, as I feel it will be much more quicker to use.

Hope this makes sense.

 

[Snakehips]

Sorry, but it's still not clear enough for me to offer a meaningful specific solution. You must remember that we do not have your appreciation of what you have and what you want to achieve.
Do try and use XL2BB to post examples with formula so that we can relate ranges .

 

[p85ki]

Sorry, I thought I did explain it as best as possible. Hopefully the mini sheet and explanation beside it will help

This data from this tab comes from the DATA tab.
This list the persons name, shift start and the type of shift they have.
Each tab will have the exact same setup.

Conditional Formatting
Currently the conditional formatting looks at whether the notes are found against each person in the DATA tab, if there is then this is highlighted.
There are blanks there which will remain blank.
For ease of the example I have only highlighted PT App Support and FT Own Rota.
Formula conditional formatting means for every change, I need to go through each row (there are about 10 on each tab and there are 6 tabs) I need to amend the conditional formatting which is becoming long winded.

The VBA is to carry out that Vlookup for the persons name and check what notes they have against them.
If they have a value then it will be highlighted accordingly to whatever I set it to be.
If they don't then it will be blank.

Currently the VBA for the vlookup is not doing it line by line or using the result to highlight accordingly.

Book1.xlsm
	A	B	C	D	E	F	G	H	I	J
2				Name	Start-End	Notes		Name	Start-End	Notes
3	Key	Number		Maria Sheesh	08:30 - 17:30	Team Lead		Jamie Alan	08:30 - 17:30	Team Lead
4	PT App Support	5		Apple Apple	07:45 - 11:45	PT App Support		Amy Sleep	07:45 - 11:45	PT App Support
5	Weekend Only	2		Jamie Tilly	08:00 - 20:00	PT App Support		James Sea	08:00 - 20:00	PT Own Rota
6	FT Own Rota	4		Charlie Desbury	08:00- 15:00	Weekend Only		Becka Snail	08:00- 15:00	Seconded Out
7	PT App Support	5		Banana Rama	08:00 - 16:00	FT Own Rota		Rebecca Noir	08:00 - 16:00	PT App Support
8	PT Own Rota	2		Drama Armour	08:00 - 20:00	FT Own Rota		Sheena Katar	08:00 - 20:00	FT First Line
9	Seconded Out	2		Bala Ala	09:30 - 14:30	PT App Support		Stacy Albot	09:30 - 14:30	Weekend Only
10				Shala lala	09:15 - 14:15	PT Own Rota		Patrick Fitz	09:15 - 14:15	FT Own Rota
11				Timmy Jimmy	06:00 - 15:00	Seconded Out		Gerald Stephens	06:00 - 15:00	FT Own Rota
12				Amy Lang	08:00 - 15:00			James Albert	09:30 - 14:30
13
14
15
16
17
18
19
App Support

Cell Formulas
Range	Formula
B4:B9	B4	=COUNTIF($J$4:$J$12,$A4)+COUNTIF($F$4:$F$12,$A4)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D5:F28	Expression	=VLOOKUP(RNG1,Sheet.Range(B2:G40),6,FALSE)="PT App Support"	text	YES

Cells with Data Validation
Cell	Allow	Criteria
D3	Any value
H3	Any value

 

[Snakehips]

Will try and take a look at this tomorrow.
Can you please let me know what your Excel CF formula is for the Range(D3:F12) as above?