Conditional formatting question

[Noel Lackey]

You asked in #11 reply
How do I test the file?
That test shows one kind of logic - how do this works.
# You can add in any cell any number.
# After that, my sample code checks all cells, especially cells which has numbers ...
... if that code noticed that there are more than one that cells value --- then it will give a comment
... if there are three same numbers, then in all of those cells has 3 in comment.
>>> if You delete one of those numbers ... then in all of those cells has 2 in comment ( that's why 'deletion' -test )

Now You wrote:
I am looking for something that would alert me if the same customer number took a third book it is ok if they take 2
... that could do other way, but then I have to know exact layout of Your used data.
= where is customer numbers? ... where are books?
You should able to create a sample Excel-file which shows some sample data ... which has some cases which gives alerts.

Hi
thank you for your detailed response please find a sample Excel file attached this is the location and layout of customer number and books

 

[vletm]

Test with this sample.

#Student ID -colors:
Light Gray = 2
Yellow = 3
Red > 3

# After You've modified D-column value, You'll get Msgbox.
# If You select any colorful D-column cell - You'll see those Books
# If You select Student ID-text (cell D1) then You'll see all rows
# If You select Books-text (cell E1) then You'll do manually Entries-check

 

[Noel Lackey]

Test with this sample.

#Student ID -colors:
Light Gray = 2
Yellow = 3
Red > 3

# After You've modified D-column value, You'll get Msgbox.
# If You select any colorful D-column cell - You'll see those Books
# If You select Student ID-text (cell D1) then You'll see all rows
# If You select Books-text (cell E1) then You'll do manually Entries-check

Thank you for all your hard work and kindness this is brilliant but is not what I am looking for I just need to be alerted if somebody takes a third book, In your system I will have to check each coloured cell individually until I find out who has taken a third book, I was hoping that the moment somebody took a third book then there three cells would change colour or alert me in some other way. In the spreadsheet Noel_Lackey_2 there are no colours under the column Student ID, How do I assign Light Grey, Yellow and Red? Thank you for all your brilliant efforts,

 

[vletm]

Did You test it?
... that sample file which I sent?
... ... I can see that You've downloaded it about 20 minutes later than I've posted it.

Your I just need to be alerted if somebody takes a third book,

Above means for me something like below
If Student ID A00323602 takes 3rd book


... You will add A00323602 into next empty cell and press <ENT> to confirm it

>> In this sample version, there is no automatic feature SO FAR, it'll ask - to check Entries like below

After [ Yes ] ... that Student ID's cells will change to yellow ... alert.



If same Student ID takes third book


For my eyes - this will give an alert.


# Your In the spreadsheet Noel_Lackey_2 there are no colours under the column Student ID,
Above snapshots are taken from that same file - for my eyes, there are colors.

# Your How do I assign Light Grey, Yellow and Red?
... by using it ... by adding books to Student IDs

# Of course,
it's possible to get a list which shows those Student IDs, who has (two,) three or more books.
... but have You asked that

# Now, You could see any Student IDs books, by selecting any Student IDs cell

 

[Noel Lackey]

Did You test it?
... that sample file which I sent?
... ... I can see that You've downloaded it about 20 minutes later than I've posted it.

Your I just need to be alerted if somebody takes a third book,

Above means for me something like below
If Student ID A00323602 takes 3rd book
View attachment 86298

... You will add A00323602 into next empty cell and press <ENT> to confirm it
View attachment 86301
>> In this sample version, there is no automatic feature SO FAR, it'll ask - to check Entries like below

After [ Yes ] ... that Student ID's cells will change to yellow ... alert.
View attachment 86300


If same Student ID takes third book
View attachment 86299

For my eyes - this will give an alert.


# Your In the spreadsheet Noel_Lackey_2 there are no colours under the column Student ID,
Above snapshots are taken from that same file - for my eyes, there are colors.

# Your How do I assign Light Grey, Yellow and Red?
... by using it ... by adding books to Student IDs

# Of course,
it's possible to get a list which shows those Student IDs, who has (two,) three or more books.
... but have You asked that

# Now, You could see any Student IDs books, by selecting any Student IDs cell

Apologies for the delay I had a busy day at work, I am just wondering when copying the new coding do I paste it over the existing coding or place it in a new window? yes your coding is working perfectly and exactly what I am looking for. Thank you for all of your help and patience. It is working 100%

 

[richerd34f]

Realized I can comment my picture. Ignore the color text at the bottom. I manually changed that. So I want the highlighted cell to have green or red text based on if it’s greater or less than the first cell in the first column. I can do this manually. But I want the same format type for all cells. The three columns repeat in groups of three and will want to be green vs red based on the previous 3 columns. Those columns are three metric values per a 2 week period. So every two weeks it will show improvement or decrease with text color. I just can’t find a way to duplicate the color format without it always applying to the original cell

 

[vletm]

richerd34f​

This thread belongs to other member.
You should open a new thread for Your challenge.
... with a sample Excel-file - including expected results.
This thread is closed now.