VBA Worksheets and Ranges

p85ki

New Member
Joined
Nov 7, 2015
Messages
22
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.

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:

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Herakles

Board Regular
Joined
Jul 5, 2020
Messages
73
Office Version
  1. 365
Platform
  1. Windows
You will need to set the rng1 range for each worksheet

VBA Code:
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

Well-known Member
Joined
May 17, 2009
Messages
5,162
Office Version
  1. 2013
Platform
  1. Windows
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.
VBA Code:
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

New Member
Joined
Nov 7, 2015
Messages
22
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

New Member
Joined
Nov 7, 2015
Messages
22

ADVERTISEMENT

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.

1618402389633.png
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,162
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Nov 7, 2015
Messages
22

ADVERTISEMENT

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

Well-known Member
Joined
May 17, 2009
Messages
5,162
Office Version
  1. 2013
Platform
  1. Windows
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

New Member
Joined
Nov 7, 2015
Messages
22
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
ABCDEFGHIJ
2NameStart-EndNotesNameStart-EndNotes
3KeyNumberMaria Sheesh08:30 - 17:30Team LeadJamie Alan08:30 - 17:30Team Lead
4PT App Support5Apple Apple07:45 - 11:45PT App SupportAmy Sleep07:45 - 11:45PT App Support
5Weekend Only2Jamie Tilly08:00 - 20:00PT App SupportJames Sea08:00 - 20:00PT Own Rota
6FT Own Rota4Charlie Desbury08:00- 15:00Weekend OnlyBecka Snail08:00- 15:00Seconded Out
7PT App Support5Banana Rama08:00 - 16:00FT Own RotaRebecca Noir08:00 - 16:00PT App Support
8PT Own Rota2Drama Armour08:00 - 20:00FT Own RotaSheena Katar08:00 - 20:00FT First Line
9Seconded Out2Bala Ala09:30 - 14:30PT App SupportStacy Albot09:30 - 14:30Weekend Only
10Shala lala09:15 - 14:15PT Own RotaPatrick Fitz09:15 - 14:15FT Own Rota
11Timmy Jimmy06:00 - 15:00Seconded OutGerald Stephens06:00 - 15:00FT Own Rota
12Amy Lang08:00 - 15:00James Albert09:30 - 14:30
13
14
15
16
17
18
19
App Support
Cell Formulas
RangeFormula
B4:B9B4=COUNTIF($J$4:$J$12,$A4)+COUNTIF($F$4:$F$12,$A4)
Cells with Conditional Formatting
CellConditionCell FormatStop If True
D5:F28Expression=VLOOKUP(RNG1,Sheet.Range(B2:G40),6,FALSE)="PT App Support"textYES
Cells with Data Validation
CellAllowCriteria
D3Any value
H3Any value
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,162
Office Version
  1. 2013
Platform
  1. Windows
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,800
Messages
5,638,416
Members
417,025
Latest member
MusterDuster

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