Excell 2007 - Changing the color of a cell if the value corresponds to the data

Hello.

I tried to use the built-in conditional formatting wizard but with no results.

I have two columns of data on my worksheet. For simplicity, let's say the days of the week in one and the months in the other. After selecting one of the days of the week in the cell (let's call it X), I would like its color to change to e.g. yellow, and for months - blue.

Thank you in advance for any hints.

You mean you want the color to change when you select the cell? Because you wrote this, do you have a selection list there and when selecting any day of the week do you want to change this color? Because you see, it's hard to guess what the poet means without this sheet ...

https://www.youtube.com/watch?v=CkDzVzQhauA

Except that in your case you choose:
Conditional formatting -> Cell highlighting rules -> Containing text

I am writing from the phone, it will be difficult with this sheet. To illustrate:

Column A: male names
Column B: female names

Cell C1: If I type anything in column A, cell C1 will turn blue. If in the same cell I select one of the values of column B - it will turn pink.

Hope it makes it easier.

Use conditional formatting, and in the condition use the count if () function and when the result of the function will be one or more, let the cell format red.

Add the second condition to the conditional formatting analogically to the first one, only for the second group of data and that's it.

added after a while
---------------------------------------
I looked at that friend pshemo13 has already told you how to do it.

Thank you for the tips - the 'Count if' function worked.

As I only had 5 points left to start a new topic, I decided to take the opportunity to save them and ask an additional question in this thread. This time I prepared myself more carefully than before.
In the attachment I present a very simplified diagram of the sheet with which I am struggling at work. I am getting tired because I am old and the new version of Excel is veeery unfriendly and somehow not intuitive for me. I am going back to the point. I gave the entire notebook in a nutshell on one sheet. We have three sections:

1. DATABASE - I put the output here - our drivers, rented (couriers), our vehicles and names of companies renting couriers
2. TABLE on which we set the cast of individual routes. Different drivers on different routes every day. In the Vehicle column, I put a vehicle (3.5 tons or 7.5 tons) for our drivers. As we do not know which car the hired courier will arrive - we only add the name of the courier company to the table (formula).
3. PRINT - this is the final sheet. The VEHICLE column should contain our vehicle number (L7: L14) or the name of the courier company (O7 11)


I need help in two places:

1. How can I make Excel enter the correct value in the PRINT column (U5: U14), that is either the vehicle number (let's say registration) or the name of the courier company?
2. Whether selecting data from drop-down lists, e.g. (D6 15) you can download (copy) the formatting (color) of the source cell by the way?

Thank you in advance with any help given.

Peter

Please.
Just change the name of the route in the "printout" and the rest of the data will complete itself.



By the way, I will suggest that if someone helps you on the forum, after logging in, press the "helped" button when he says

Thanks a lot for your help! Works great. That's what I meant. I will just ask about the second point from my post: is it possible that the color of the source cell's fill can be transferred to another, eg From Database to Printout? So that vehicles, names of drivers, couriers and companies retain the colors assigned to them in the Database? Manually formatting each of the (40 in the original) rows in two columns is a Sisyphean job considering that in the original, cells are merged and I can't just copy the format.

When it comes to transferring formatting, excel has virtually no functions. This can be done only by macro (VBA) or by "typing" through conditional formatting and in Excel 2007 and above, because lower conditions can only have max 3 conditions.
Those who know what-so-so programming with VAB can do just fine. Those who have no idea will collapse at the beginning, because macro is governed by laws other than functions. Moving one cell may affect the operation of the entire macro.

So, formatting with excel functions will not go through. There are conditional formatting (but this is "spelling") and VBA, but everything matters here: the position of the cell, the target of the cell, so you can only work on the result file (with the original setting of cells). In addition, any modification of the spreadsheet may involve a modification of the macro code because it is already programming, so ...

In the example, you have conditional formatting as. See terms and conditions. Of course, you do not format each cell separately, but the entire group of cells (in this case, a column) of course including $ or not in the condition.
Of course, you only change the names of the routes, "automatic" will work. You implement the changes using the "guidelines" for the route in the first table and the colors are in conditional formatting.

Thanks for the information. At least I know where I am standing. I will work on your example - there will be something to do on the weekend

To save on points, I'd like to pull on the topic and bring up another cell formatting problem.

There are two columns with data in the enclosed notebook:

Column A - Data source
Column B - a drop-down list that retrieves data from Column A

The question is, is there any simple way that after selecting any value in the list (B) - its counterpart (A) - it changes the color of the cell? (So Walczak and Szymanek in Column A should now have e.g. a blue cell background).

Why am I asking? In the original, each of the columns is much longer and each is on a different Sheet (the same Notebook). When selecting the value (s) in Column B - after going to Column A (different Sheet) - I have a colored confirmation of which names I chose.

Basically, I mean a simple (visual) verification of data that all names have been assigned somewhere on a given day and no one has been omitted or duplicated in the Sheet with the drop-down list (B).

Thank you in advance for your help.

Peter.

Only a macro to write this you need this sheet. Actually, it would be easier to generate a skip / duplicate list

Thanks for the quick reply. I can't upload the original notebook now because I'm not at work. But I would be able to adapt the macro if you would send a hint based on the inserted example. Although I must admit that I would also like to see what this omitted / duplicated list would look like - I have never tried it. I would appreciate the example used on my file.

Well ... then fight the turquoise red selected duplicate ...

Macro is the last resort.
After all, this can be easily done with conditional formatting.


added after a while
---------------------------------------
And as for the presented macro, it works, but if I could, a word (two) :
It is a bit of a load for the computer because it is performed after each change in the sheet - it would be necessary to limit the operation to changes occurring only in the scope of the second table.
And the second thing:
after completing the procedure, it is worth resetting the variables "declared" with set, because they are not necessarily stored in the memory.

First of all, I did not ask for advice, if I need it, please. Secondly, if you care about each byte in this way, you should probably include the corrected file ...

Review from microsoft comunity on:

There is no need to set any local variable to Nothing at the end of a sub, because the memory manager does this automatically.

If you call a sub the (VBA-) stack is increased automatically, any local variable is place inside this memory structure and the stack shrinks automatically if the sub ends and any local variable is removed.

Only global object variables in normal modules should be set to Nothing to release the object from memory. Forget anything else.

As for the handler, you are actually right, except that the author has different scopes ...

Gentlemen, thank you again for your help. As usual, my colleague's method marek003 it's the proverbial bull's eye: easy and accessible (easy - as you've already seen it ). As for my friend Lesławek - unfortunately I can't comment - because it looks like the macro doesn't work for me. The file icon itself shows an exclamation point - something wrong? The new Excel is just killing me - to find some options, I have to 'google' how to get there (e.g. to macros).

As I only have 4 points (opening of the 1st new topic), I will appear here from time to time with a new question. I am correcting a document that facilitates the management of my shift at work. A lot has been mixed up there and in my free time I try to straighten it out. When I'm finished - I'd like to have it subjected to a professional control. The form of 'expressing gratitude' - to be agreed :)

Best regards and have a nice weekend!

I'm too old for such scuffles
Lesławek , don't get annoyed, I wasn't going to offend you (and I don't know why to treat my suggestion this way).
I am from an old school, where they taught me that I should clean up after myself and not pass it on to others, in this case the memory manager. Besides, the "good rules" of code writing have a plus when you start to write in other languages that may be less "modern".
But on the other hand, you are right : If a given programming language already allows it, why "clutter" the code with unnecessary commands, so ok.

lesławek wrote:

... if you care about each byte, you should probably include the corrected file ...

And here I admit that I do not know what to improve (except that the colors do not correspond to the version below 2007, but it does not affect bytes)
But if something is wrong, write it, because I am open to any suggestions (a person learns all his life) and if I do something wrong, I will try to correct myself.

Gandalf67 To easily "access" macros, turn on the "Developer" tab on the "menu" ribbon
File - Options - Customize the ribbon - and select "Developer" in the right pane and then OK.
Through this menu you will have access to the code as well as to other excel "curiosities".

Well, I must admit that I was also a bit overwhelmed, your conditional formatting way is actually better, in this case. Of course, until the bases start growing in terms of the number of rows, the macro can be easily prepared for this, but I did not do it by setting the areas rigidly ...

Welcome back.

I have another problem with conditional formatting of cells. This time I would like to avoid duplicating values (surnames) inserted in one column using the dropdown list. The list goes on and it would be a very helpful option to warn you that a name has already been used. Unfortunately - I cannot use the automatic option of highlighting duplicates, because in addition, in the column I put two cyclically repeated values - which I would like to be omitted when searching for duplicates. Below I am attaching a simplified sample file.

I would like to:

* Names accidentally duplicated have been highlighted with some warning a color or a message would be generated stating that the given name has already been selected.
* Duplicate entries CANCELED and MOVED had the current fill color like the one in the example - i.e. they were ignored by duplicate searches.

Thank you in advance for your help and possible examples of solving the problem

Here you go.

Wonderfully! Many thanks for your help - once again.