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:
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?

Thank you so much. I'm so suprised that I can't find anything online that discusses how to use that vlookup value result to create the highlight. It only discusses how to enter a value in or a message box :(

The CF looks at the name, then goes to the DATA sheet (name manager I've labelled it as Data_Details).
The Data sheet contains name, Login ID, team leader, department, start time, end time, shift details, notes)

CF Formula

Rule
=VLOOKUP($D3,Data_Details,7,FALSE)="App Support"

Applies to:
=$D$3:$F$12
 
Upvote 0

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Thank you so much :)

I have been trying to find online how to store the result of a vlookup so that I can format it accordingly but there doesn't appear to be anything other than outputting the result of a singular check (and not check each column. :(

I am now thinking about this and it maybe that I need to create a case type query instead. I have around 16 notes to consider for each range.
 
Upvote 0
Good Morning :)
Ok, I'm able to give this some attention and see if I can help.

Firstly, the vba below hopefully shows a couple of ways to change the VLookup formula and have it apply to the whole range.

VBA Code:
Sub VlookTest()

Set Rng = Range("D3:F12")
      
   With Rng
    .FormatConditions.Delete
    
 'Two examples below   Rem them out , in turn to see how / if how they apply the formatting to the range
    
  ' .FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=VLOOKUP($D3,Data_Details,7,FALSE)=""Full Time"""
        
    .FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=VLOOKUP($D3,Data_Details,7,FALSE)=$F3"
        
        
    'Test cell highlihting
    With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent3
        .TintAndShade = 0
    End With
    End With
   
End Sub

I doubt that is the full solution but see if it at least enlightens you on that aspect.
 
Upvote 0
Thank you so much Snakehips - that is working perfectly.

The one you commented out is the one that is actually working to the expectation (highlighting only the columns it requires).

I now need to make this run through each sheet but this will give me time to learn and play about with what I have available.

Thank you again for your patience to show this to me. :)
 
Upvote 0
Thank you so much Snakehips - that is working perfectly.

The one you commented out is the one that is actually working to the expectation (highlighting only the columns it requires).

I now need to make this run through each sheet but this will give me time to learn and play about with what I have available.

Thank you again for your patience to show this to me. :)
You are most welcome.
I have to admit that I still somewhat confused over what you are doing but if it helped then that is all that matters.
 
Upvote 0
:D

It is complex but very simple at the same time.

The spreadsheet is plugged into a DB so pretty much all the data comes out from there, the spreadsheet view is purely for the audience to see items at different levels (higher management to lower management).

In simple terms, it's just seeing how much staff are in each team, how many people are being moved about and decisions around recruiting for particular times/shifts departments.

There are lots more tabs that does other things and at much higher level. It's safe for both upper/lower management to see it all as it's useful details to know. :)
 
Upvote 0
Ahhhh - I think I may need your assistance again Snakehips :(

I thought this was going to be much simpler tbh but it doesn't appear so :(

It works to do one of the CF, so when I attempt to copy and paste the inital code that works for the 2nd CF, it only completes the 2nd CF and not the 1st.
I attempted to remove the .FormatConditions.Delete for both but it didn't do anything different.
Any reason why the code is doing that? I suspect I need to say do not remove the 1st CF and continue doiing the additional CF.



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($D5,Data_Details,6,FALSE)=""App Support"""

'Test cell highlihting
    With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent3
        .TintAndShade = 0
End With
End With

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($D5,Data_Details,6,FALSE)=""Tech Support"""

'Test cell highlihting
    With .FormatConditions(1).Interior
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0


End With
End With

Next WrkSht

End Sub
 
Upvote 0
Good Morning :)
I will take a look at this for you but will not be able to do so until this afternoon.

In the meantime can you please confirm / deny / or correct:
You have more than one 3-column ranges on 6 sheets?
You wish to colour format each row of each range according to the 'Notes' if they agree with the listing in data sheet?
Please expand on this if the rules are more complex.
You do this with say 16 different Conditional Formatting rules?

If your 16 rules are good, why do you need to change them now and again?
How often do you have to re-hash the rules?

Is it possible that using 'hard' formatting that might be more easily changed, when necessary, is better than using CF?

Again, remember that what is as clear as day to you about what you have and what you want is totally obvious to you. It is a mystery to me, until clearly pointed out and put into context.
I can help you with aspects of vba detail that you are struggling with but, that is of no real value if you are pursuing an approach that is maybe not the right way, and I cannot see that.

Must dash................
 
Upvote 0
Thank you for coming back Snakehips and really appreciate your time and effort here.

I've put your query into questions below so hopefully it's easier to follow. Also I did debate about whether the VBA code was the correct way after analysing this. It seems it maybe the 'easiest' way but now not to sure.

1) You have more than one 3-column ranges on 6 sheets?
1a) Yes I have about 9 columns per sheet:

Set rng1 = WrkSht.Range("D5:F33")
Set rng2 = WrkSht.Range("H5:J33")
Set rng3 = WrkSht.Range("L5:N33")
Set rng4 = WrkSht.Range("P5:R33")
Set rng5 = WrkSht.Range("T5:V33")
Set rng6 = WrkSht.Range("X5:Z33")
Set rng7 = WrkSht.Range("AB5:AD33")
Set rng8 = WrkSht.Range("AF5:AH33")
Set rng9 = WrkSht.Range("AJ5:AL33")

I originally had the last row setup, but realised there was more details applied further down the spreadsheet in coloumn F. I can control the spreadsheet that they all have the same data across each tab by doing the above.

2) You wish to colour format each row of each range according to the 'Notes' if they agree with the listing in data sheet?
Please expand on this if the rules are more complex.

2a) The example I showed was to do that Yes and currently this is how it is working. I have realised the reallity of this is that the information already exists in the raw data tab, so to avoid duplication I am now just doing a CF vlookup to see if they have a note against them, if they do, then colour code them according to the Conditional Formatting rule. There is a key that defines what they are.

Example CF : VLOOKUP(D5,Data_Details,6,FALSE) = "App Support"
Colour code Light blue

To note: As I have hard coded that, my intentions are to make that dynamic to a list in the parameter tab in a worksheet so that it is easier for the user to amend as they please.

Example: I've created a column in a parameter sheet tab which lists all the different types of notes possible.
I've also created those notes as a list in the DATA tab where they can only select from that list in a drop down and cannot add their own list of Notes.

If against the Notes section the vlookup for the persons notes are blank, then they have a box standard normal shift and no colour applied.
If the persons notes have a 'Note' and say they have App Support, then a conditional formatting rule is applied, which in the example above it will be blue.


3) If your 16 rules are good, why do you need to change them now and again?
How often do you have to re-hash the rules?

This all started off at a very low level, 5 rules and no more than 5 teams and around 3 tabs. As you can see this has crept up.
The CF for each sheet is to go against each column range and add the CF against the new value I need (which is currently hardcoded).

Example: I have 9 fields per tab. If I now need to add "Point A", I will need to add the CF in for 'Point A' 54 times due to the amount of columns I need to do this against.
They have now just said they want to change the colours as it does not look right which means it's a whole load of work and headache and feel a long term solution is not to keep adjusting this spreadsheet.

The majority is now in a DB, so the last part is the conditional formatting to 'control' as best as possible.

4) Is it possible that using 'hard' formatting that might be more easily changed, when necessary, is better than using CF?

My ideal way of thinking to control how this would work:

a) Parameter Tab: Column A: A parameter sheet that contains a list of Notes that they can apply against people. They can add/subtract as they like and the code would take care of that.
b) Parameter Tab: Column B: A colour index that users can look up online and put a Hex value in there to say what colour they wish to highlight. This will mean they will have control of their own colour codes.
c) 6 Tabs with data: Where the Data tab has Notes against the person that it will highlight that user according to the hex colour information in column B.

By doing the above, the user has control of what list of Notes will always be displaying and what colour they wish to assign that note.

Again the purpose of this spreadsheet is to identify how many people are listed in different departments that may not be in their own team and also the type of shift they have.
It's for a quick glance at high level and then for those that wish to analyse (which they generally do) see which teams have so many people assigned to them to 'resolve' furture issues.

I hope the above gives more insight to how it's used. Hopefully VBA is I believe the better method, but happy to hear any other possible solutions if you feel it's not. :)
 
Upvote 0
Thank you for coming back Snakehips and really appreciate your time and effort here.

I've put your query into questions below so hopefully it's easier to follow. Also I did debate about whether the VBA code was the correct way after analysing this. It seems it maybe the 'easiest' way but now not to sure.

1) You have more than one 3-column ranges on 6 sheets?
1a) Yes I have about 9 columns per sheet:

Set rng1 = WrkSht.Range("D5:F33")
Set rng2 = WrkSht.Range("H5:J33")
Set rng3 = WrkSht.Range("L5:N33")
Set rng4 = WrkSht.Range("P5:R33")
Set rng5 = WrkSht.Range("T5:V33")
Set rng6 = WrkSht.Range("X5:Z33")
Set rng7 = WrkSht.Range("AB5:AD33")
Set rng8 = WrkSht.Range("AF5:AH33")
Set rng9 = WrkSht.Range("AJ5:AL33")

I originally had the last row setup, but realised there was more details applied further down the spreadsheet in coloumn F. I can control the spreadsheet that they all have the same data across each tab by doing the above.

2) You wish to colour format each row of each range according to the 'Notes' if they agree with the listing in data sheet?
Please expand on this if the rules are more complex.

2a) The example I showed was to do that Yes and currently this is how it is working. I have realised the reallity of this is that the information already exists in the raw data tab, so to avoid duplication I am now just doing a CF vlookup to see if they have a note against them, if they do, then colour code them according to the Conditional Formatting rule. There is a key that defines what they are.

Example CF : VLOOKUP(D5,Data_Details,6,FALSE) = "App Support"
Colour code Light blue

To note: As I have hard coded that, my intentions are to make that dynamic to a list in the parameter tab in a worksheet so that it is easier for the user to amend as they please.

Example: I've created a column in a parameter sheet tab which lists all the different types of notes possible.
I've also created those notes as a list in the DATA tab where they can only select from that list in a drop down and cannot add their own list of Notes.

If against the Notes section the vlookup for the persons notes are blank, then they have a box standard normal shift and no colour applied.
If the persons notes have a 'Note' and say they have App Support, then a conditional formatting rule is applied, which in the example above it will be blue.


3) If your 16 rules are good, why do you need to change them now and again?
How often do you have to re-hash the rules?

This all started off at a very low level, 5 rules and no more than 5 teams and around 3 tabs. As you can see this has crept up.
The CF for each sheet is to go against each column range and add the CF against the new value I need (which is currently hardcoded).

Example: I have 9 fields per tab. If I now need to add "Point A", I will need to add the CF in for 'Point A' 54 times due to the amount of columns I need to do this against.
They have now just said they want to change the colours as it does not look right which means it's a whole load of work and headache and feel a long term solution is not to keep adjusting this spreadsheet.

The majority is now in a DB, so the last part is the conditional formatting to 'control' as best as possible.

4) Is it possible that using 'hard' formatting that might be more easily changed, when necessary, is better than using CF?

My ideal way of thinking to control how this would work:

a) Parameter Tab: Column A: A parameter sheet that contains a list of Notes that they can apply against people. They can add/subtract as they like and the code would take care of that.
b) Parameter Tab: Column B: A colour index that users can look up online and put a Hex value in there to say what colour they wish to highlight. This will mean they will have control of their own colour codes.
c) 6 Tabs with data: Where the Data tab has Notes against the person that it will highlight that user according to the hex colour information in column B.

By doing the above, the user has control of what list of Notes will always be displaying and what colour they wish to assign that note.

Again the purpose of this spreadsheet is to identify how many people are listed in different departments that may not be in their own team and also the type of shift they have.
It's for a quick glance at high level and then for those that wish to analyse (which they generally do) see which teams have so many people assigned to them to 'resolve' furture issues.

I hope the above gives more insight to how it's used. Hopefully VBA is I believe the better method, but happy to hear any other possible solutions if you feel it's not. :)
Thanks for that, I will read and digest and see what I can do.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

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
Back
Top