Data / Conditional Formatting

[TonyNZ]

Hi

Please see attached.
As a sample, I have my source data in Column A and B.
Data I want to be recorded in is under Mondays to Fridays. (Columns E to I)

My requirement:
As I type Code "1" in Cell E6, it should automatically record as English.
As I type Code "6" in Cell E7, it should automatically record as Dance.
and so on.

(Ultimately, I expect to have about 50+ Codes and their relevant entries)

Additionally, I want English to be shown as in Cell J24 (of the attached workbook)
Similalry, I want Dance to be shown as in Cell J25 (of the attached workbook)
and so on.

And if need be can I use List Form box for making entries with similar results?

Any help would be greatly appreciated.

Thank you

Regards
Tony

 

[AliGW]

Two questions:

1. Why bother with codes? Surely just a drop-down list would suffice?
2. Why do you need English and dance to appear in the shaded cells - what's the ultimate aim?

Actually, three questions!

3. Why not use timetabling software? There are a lot of variants out there.

 

[vletm]

My (TonyNZ) requirement:
As I type Code "1" in Cell E6, it should automatically record as English.
>> activate Your 'English'-text > Ctrl+C > choose E6 > Ctrl+V
As I type Code "6" in Cell E7, it should automatically record as Dance.
>> activate Your 'Dance'-text > Ctrl+C > choose E7 > Ctrl+V
and so on.

 

[GraH - Guido]

Part 1 done. For some reason I can't get the code to apply to the target cell the same format as in the code table.
If you simply extend the code table with all your 50+ codes, it should work.
EDIT: Code was easily found via google btw...

 

[TonyNZ]

Dear GraH - yet again you have come to my rescue. Thanks muchly. Have solved Part 2 also. I have simply applied conditional formatting in the destination cells, and volla, it works.
This works perfectly fine till 9. However, from 10 onwards, it become ambigous. I have attached file again. Please refer to all entries in bold underline italics with my comments in bold red, in cells D15 to D21. I am sure coding need little tweaking but not too sure how to do it. Please advice.
One more question. Any particular reason that this file has to be in binary format? Can it be in macro enabled format?
Regards
t

 

[TonyNZ]

Thanks Ali, my comments in bold:

Two questions:

1. Why bother with codes? Surely just a drop-down list would suffice? Yes and no. Not in my situation. It will not be fast and efficient with 50 entries in the dropdown list. User will end up scrolling up and down to find the required entry. Keying in numbers and relevant entry coming on automatically, in my view, will be much faster and easier (and not frustrating either) for the user.
2. Why do you need English and dance to appear in the shaded cells - what's the ultimate aim? Brain recognize colur much much faster than reading something. (make sense lol)

Actually, three questions!

3. Why not use timetabling software? There are a lot of variants out there.
This is the sample file with dummy data to develop the concept. The end product is not for the teaching institution.

btw I am also passionate and Enthusiastic self-taught user of MS Excel who's always learning! And I do this only to help out my colleagues. Brings immense satisfaction when you see smiles on others faces which no amount of money could buy.

 

[TonyNZ]

Dear all, Its 00:45 hrs in this part of the world (NZ). I better go to sleep now. Will return to find magic answers in the morning. Thanks again.

 

[p45cal]

See the attached, enable macros and see if it floats your boat. I've not used numbers, only letters.
In the drop downs, you can select with a dropdown or type letters and, because I've sorted the entries alphabetically you'll be able easily to use a combination of the two (type a letter or two, then use the drop down if necessary).

Attachment deleted, I've updated this file in msg#11 below.

 

[p45cal]

Oops, somehow I hadn't noticed others' responses, sorry.
re:

For some reason I can't get the code to apply to the target cell the same format as in the code table.

In the attached is an adaptation of Grah - Guido's solution. I've done away with conditional formatting altogether, and just copy the cell lock, stock and barrel.

 

[GraH - Guido]

One more question. Any particular reason that this file has to be in binary format? Can it be in macro enabled format?

No, no reason, .xslm is fine. I changed the replace line too.

 

[p45cal]

Update:
See the attached, enable macros and see if it works. Again, I've not used numbers, only letters.
In the drop downs, you can select with a dropdown or start typing letters and, because I've sorted the entries alphabetically you'll be able easily to use a combination of the two (type a letter or two, then use the drop down if necessary).
I've kept conditional formatting, but only in the Schedule.

 

[TonyNZ]

Hi P45
Unfortunately, I am not able to open this and clear security (Blocked content) due to blockage my official policies. i did not know that before. i will have a look tonight on my laptop at home, and will come back to. Kind regards t

 

[Marc L]

Hi !​

​

According to the post #5 attachment, as a beginner starter :​

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, [tSchedule]) Is Nothing Or Target.CountLarge > 1 Then Exit Sub
    If Not IsNumeric(Target.Value2) Then Exit Sub
    Application.EnableEvents = False
    Target.Value2 = Evaluate("IFERROR(VLOOKUP(" & Target.Value2 & ",tCodes,2,FALSE),"""")")
    Application.EnableEvents = True
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[TonyNZ]

Update:
See the attached, enable macros and see if it works. Again, I've not used numbers, only letters.
In the drop downs, you can select with a dropdown or start typing letters and, because I've sorted the entries alphabetically you'll be able easily to use a combination of the two (type a letter or two, then use the drop down if necessary).
I've kept conditional formatting, but only in the Schedule.

Dear P45

Finally got time now to look at it. Yes, this sounds good. I will still explore as to which of the solution could be the best one for the project currently in hand. Suffice to say that I have got more than what I was expecting and each of the solution suggested in this forum will be able to put to good use in one or the other form. Efforts made by each contributor will not go to waste. I am learning a lot.

I have just one last question. How we can make this work on any additional worksheets to be added to same workbook. Yes we can make duplicate of this and add and rename and change the name in the vBA. Can there not be a solution (code) taht this applies to any number of worksheet(s) being added? I am sure this could be done. Any advice please?

Regards
t

 

[TonyNZ]

Hi !​

​

According to the post #5 attachment, as a beginner starter :​

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, [tSchedule]) Is Nothing Or Target.CountLarge > 1 Then Exit Sub
    If Not IsNumeric(Target.Value2) Then Exit Sub
    Application.EnableEvents = False
    Target.Value2 = Evaluate("IFERROR(VLOOKUP(" & Target.Value2 & ",tCodes,2,FALSE),"""")")
    Application.EnableEvents = True
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

Hi Marc
I have tried this, and yes, this works too.
I have just one last question. How we can make this work on any additional worksheets to be added to same workbook. Yes we can make duplicate of this and add and rename and change the name in the vBA. Can there not be a solution (code) taht this applies to any number of worksheet(s) being added? I am sure this could be done. Any advice please?
Regards
t

 

[TonyNZ]

@GraH - Guido
@Marc L

Thanks very much for your help.
Both solutions works perfectly fine.
and yes, this is THE perfect solution.

In the final product, I will have tCodes in one data sheet. the final product will have about 20 worksheets.
The code just now needs refinement so that it applies to every additional worksheet added from time to time.

Could the refinement be done in the existing workbook in above message #10? I am sure it could be done. Please let me know.

Regards
t

 

[TonyNZ]

@p45cal

Hi
Dropdown solution provided by you is also very helpful, however, looking at the scope of my current project the solutions provided by @GraH - Guido and @Marc L are the most suitable one. Having said that, I will use your solution in a different project. It will be put to good use, be assured of that. Thanks again for your help.
Regards
b

 

[GraH - Guido]

@GraH - Guido
@Marc L

Thanks very much for your help.
Both solutions works perfectly fine.
and yes, this is THE perfect solution.

In the final product, I will have tCodes in one data sheet. the final product will have about 20 worksheets.
The code just now needs refinement so that it applies to every additional worksheet added from time to time.

Could the refinement be done in the existing workbook in above message #10? I am sure it could be done. Please let me know.

Regards
t

Glad we could help, I actually learned a few thing from Marc's and Pascal's posts as well.
As for the code to work on each sheet, I think you need to repeat the code on each sheet. Or at least have a line of code that reacts on the change event and calls the replacement sub(). But that is just a beginner talking
The lookup table can be on a single sheet and does not have to be repeated.

 

[Marc L]

• For several worksheets and variable color formatting, the less expensive in worksheets ressources
  is the Find & Copy methods like yet shown by Guido so conditional formatting is not necessary …
  
  
• For several worksheets, differents ways to implement that, as usual it depends on the workbook itself,
  so on the next you must attach (saved as .xlsm or .xlsb) according to your real need …
  (at least two worksheets and a single colors codes table, no ever needs named ranges
  if real tables - aka ListObjects - are used for codes list and each worksheet "day & time" list)

 

[Marc L]

As for the code to work on each sheet, I think you need to repeat the code on each sheet.

Can be avoided with an unique event at workbook level instead of each worksheet …​

​

 

[p45cal]

For multiple worksheets:
Most solutions have involved event handlers like:
Private Sub Worksheet_Change(ByVal Target As Range)
and
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
in the sheet concerned's code-module.
There are similar event handlers in the ThisWorkbook code-module:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
and
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

The latter ones take an additional argument Sh, which is the sheet that has triggered the event.
Use this to (a) decide whether to do anything at all and (b) if something is to be done what sheet to do it on.

You've said that tCodes will be on a separate data sheet, so I doubt very much you'd want the manipulations to take place on that sheet, so you need a way to identify which sheets you don't want it to work on (or which sheets you DO want it to work on - it doesn't matter). At its simplest you may want the code to run on ALL the sheets except your tCodes-containing sheet; let's say you called that sheet Data. So your code could first check the sheet name:
If Sh.Name <> "Data" then
'...your code here
End if
Remembering that any unqualified range references to the sheet should be prefixed with Sh., but references using Target should be fine left as-is.
Lines such as Set r = Range("tSchedule") will need a bit of handling. Excel won't allow tables to be named similarly in the same workbook. When you copy the sheet, you'll find your tSchedule table renamed to perhaps tSchedule2 or perhaps tSchedule4 etc. There are many ways to skin a cat, but one way to identify the table if the table always sits at cell D6 is to replace that line with:
Set r = Sh.range("D6").listobject.databodyrange

There shouldn't be the need for any code at all in the individual worksheeet code-modules.
That's it.

 

[Marc L]

or which sheets you DO want it to work on

Maybe I already have an easy way to identify such worksheets (and without of course any named range),​

the reason why I need an attachment based upon the real workbook …​

​

An idea from many : use only a table - ListObject - on worksheets needing the event​

and not for example for the codes list worksheet as a range starting from A1 is easy to search in (like the initial attachment) …​

​

 

[Marc L]

According to the initial attachment, Sheet1 contains the codes list from A1 cell but not as an Excel table (not a VBA ListObject)​

so it's very easy to detect an input on any table from any worksheet via the event at workbook level​

as I wrote in my previous post : just see my below code !​

​

If some table does not need to be connected with this event whatever its worksheet,​

like for example in the post #5 attachment where the codes list is a real table (so a ListObject),​

it's easy to check if the table first header is "Monday" like the codeline in comment in my below code.​

In this case you must activate this codeline just by deleting its first character aka ' …​

​

My below code does not need any named range neither any conditional formatting​

as the color formatting must yet be within the codes list.​

​

Paste this code to the ThisWorkbook module and remove any old Worksheet_Change procedure from worksheets modules :​

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim Rg As Range
    If Target.CountLarge > 1 Then Exit Sub
    If Target.ListObject Is Nothing Or Not IsNumeric(Target.Value2) Then Exit Sub
'    If Target.ListObject.HeaderRowRange(1).Value2 <> "Monday" Then Exit Sub
    Set Rg = Sheet1.UsedRange.Columns(1).Find(Target.Value2, , xlValues, xlWhole)
    Application.EnableEvents = False
    If Rg Is Nothing Then Target.Clear Else Rg(1, 2).Copy Target: Set Rg = Nothing
    Application.EnableEvents = True
End Sub

You may Like it !​

​

 

[TonyNZ]

@Mark L @GraH - Guido @p45cal
Brilliant, I will recreate fresh work-book replica of my exact requirement, save it in required format and attach.
@p45cal correct "At its simplest you may want the code to run on ALL the sheets except your tCodes-containing sheet; let's say you called that sheet Data"
Regards
b

• For several worksheets and variable color formatting, the less expensive in worksheets ressources
  is the Find & Copy methods like yet shown by Guido so conditional formatting is not necessary …
  
  
• For several worksheets, differents ways to implement that, as usual it depends on the workbook itself,
  so on the next you must attach (saved as .xlsm or .xlsb) according to your real need …
  (at least two worksheets and a single colors codes table, no ever needs named ranges
  if real tables - aka ListObjects - are used for codes list and each worksheet "day & time" list)

totally agree, Yes, conditional formatting is hugely draining on resources.

 

[Marc L]

I will recreate fresh work-book replica of my exact requirement, save it in required format and attach.

Try first my post #23 code with your real workbook then in case of any issue you can create & attach a replica …​

​