Solved

"J" column have the (Q) original color on top of row given color when being refresh.

Posted on 2016-10-01
30
36 Views
Last Modified: 2016-10-05
Hello Experts Exchange community, helper and users. Need help with this two method to be implemented.

***In "J" column when today date is on the same date on the "B" column. Show on the "J" column the following word (TODAY) with parenthesis bold in red.

***In "J" column when the "Q" letters are being presented, when date are updated/refreshed also update the color that has been given to them on top of any color assign on the row.

For a better understanding and a more in depth demonstration, please view the following link where it will be more descriptive. Thanks again for your time and help.

http://www.screencast.com/users/Omarantonio1231/folders/New%20Folder/media/a669ff7e-ce98-4641-87f8-8e93dad6424a

FIle will also be attach on this question.
BK-TO-DO-LIST.xlsm
0
Comment
Question by:Omar Hernandez
  • 17
  • 13
30 Comments
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Your link seems to require me to log in to screencast and/or download Flash and I'm reluctant to do either. In any case in the workbook you posted there are no dates in column J.
0
 

Author Comment

by:Omar Hernandez
Comment Utility
Hello there martin liss, thanks for responding, ok no problem on colomn "J"

***the dates that are on column "B" is taken from there and there is a formula programmed to show on column "J" if you click on column "B" you can change a date to a present one and once you do it will update column "I" and "J" with the appropriate information input from column "b'
0
 

Author Comment

by:Omar Hernandez
Comment Utility
Is there another place i can update the video clip Martin so that you may see where i need help
0
 

Author Comment

by:Omar Hernandez
Comment Utility
Hello martin liss try any one of these two link my friend and let me know if it works.

https://sendvid.com/6fru6i4b

https://vid.me/1gdx
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
When I clicked on your first link above it stated to play but I immediately got a fake warning that my Mac was infected with 3 viruses and it tried to lock my browser so I couldn't exit. I was able to get out of it but from now on if you want my help you are going to have to explain in a post what you want because I won't be opening any more links.
0
 

Author Comment

by:Omar Hernandez
Comment Utility
Wow, that is a first. Sorry for any inconvenience just wanted to make it easier for both of us.
 
on Column "J"

The dates that are on column "B" is taken from there and there is a formula programmed that will be show on column "J" if you click on column "B" you can change a date to a present or future dates, and once you do it will update column "I" and "J" with the appropriate information input from column "b",

***In "J" column when today date is on the same date on the "B" column. Show on the "J" column the following word (TODAY) with parenthesis bold in red.

***In "J" column when the "Q" letters are being presented, when date are updated/refreshed also update the color that has been given to them on top of any color assign on the row.
0
 

Author Comment

by:Omar Hernandez
Comment Utility
Hey Martin liss i really appreciate your help, if you are less skeptical of YouTube site i did uploaded there. If it is in you to try it out that way. Remember my friend i am just trying to find a better way to communicate, so that you may understand it with efficiency. If you did not understood the detail on the top message, give this a try if you may, Go to the actual YOUTUBE page and type on the search bar the following. ( Update Q COLORS AND SHOW TODAY WHEN TODAY DATE ) and you will see it under the user kalua1717. Hope this is great for you as a solution in viewing the video at a safe location.

Update Q COLORS AND SHOW TODAY WHEN TODAY DATE
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
In Module1 of this workbook I added a UDF (User Defined Function) which I called Organizer2 for the lack of a better name. Cells J37, J38 and J43 use that UDF as a formula. See if it gives you what you want.

The UDF can be moved from Module1 to any module you like, and you can name it something else  as long as you change all references to the name in the UDF.
28973745.xlsm
0
 

Author Comment

by:Omar Hernandez
Comment Utility
Hello Mr Martin Liss wanted

***Column "J" The ( TODAY ) word to be in bold, Times new Romans and with the color (RED) Align only (TODAY) ON CENTER.
on any present date being on the same date as the "B" column.

***Column "J", where the (QQQQQQ) are being displayed i would like it be shown as follow. KEEP ALL (Q - QQQQQQ) ALIGN TO THE LEFT
5 or more Q will be Q's in blue color
3 and 4 Q will be Q's in Orange color
1 and 2 Q's will be Q's in Red color.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
You've got a lot going on in your workbook:)

Try this version.
28973745a.xlsm
0
 

Author Comment

by:Omar Hernandez
Comment Utility
Thanks for the update Martin Liss, we are almost there.

When you change your date on the pc and then click on the "REFRESH" button i want to see the following

***Column "J", where the (QQQQQQ) are being displayed.
---If i click on refresh on 5 or more Q will be Q's in blue color

---If i click on refresh on 5 or more Q's when i click on the updated/refresh button when updating to 3 and 4 Q change blue color to Orange Q's color.

---If i click on refresh on 3, 4, 5 or more Q's when i click on the updated/refresh button when updating to 1 or 2 Q change blue or orange color to Red Q's color.

Please if you may Go to (www.youtube.com) and on the search bar type the following so that you may see what i clearly want friend.
(Update Q COLORS AND SHOW TODAY WHEN TODAY DATE)
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
I assume that the "Refresh" button is the orange rectangle  named "Rectangle: Beveled 11".

I didn't actually change the date on my PC but I think the code in this version does what you want. BTW in your video you mention that you might  want "(Today)" to be black. If so then in the Worksheet_Change event for the GENERAL sheet change the Cells(Target.Row, 10).Font.Color = colors(1) line to Cells(Target.Row, 10).Font.Color = colors(0).
28973745b.xlsm
0
 

Author Comment

by:Omar Hernandez
Comment Utility
Hello there Martin Liss thanks for your time and help, progress just a tiny inconvenience please check the new video on YouTube that i have made available(uploaded)

please on the search bar copy and paste the new title.

Update Q colors and show today Part 2

Thanks again and hope this will help a ton.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Try this. At the bottom of the "refresh" sub in Module15 you'll find code like this. Change line 5 as shown.

    With ActiveWorkbook.Worksheets("GENERAL")
        lngLastRow = .Range("J1048576").End(xlUp).Row
        For lngRow = 1 To lngLastRow
            If InStr(1, .Cells(lngRow, "J").Formula, "=Organizer2") > 0 Then
                .Cells(lngRow, "J").Formula = "=Organizer2(" & "I" & lngRow & ")"
            End If
        Next
    End With

Open in new window

0
 

Author Comment

by:Omar Hernandez
Comment Utility
tried it, No luck my friend
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
Please attach your current workbook.
0
 

Author Comment

by:Omar Hernandez
Comment Utility
Hello there Martin liss, here you go friend
28973745b.xlsm
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
When you say "No luck" I need to know exactly what didn't work since the change we just made in post ID: 41828614 had to have at least partially improved the outcome.

Note that the "refresh" sub that I modified looks for the "=Organizer2" formula in column J and updates the cell in column J if it's there. As it turns out in your workbook, cell J43 had no formula so refreshing did not change that cell. Rather looking for "=Organizer2" it would be better to know when it should be there. Can I assume that if columns "A" and "B" have dates then the formula should be there?
0
 

Author Comment

by:Omar Hernandez
Comment Utility
Hello there Martin Liss The Q's being displayed showing the amount of Q's of the dates that is left is correct, but when it comes to the following

This it the only thing it needs fixing
***Showing the proper color it should have and it is not, 5 and up Q's is blue, when turning blue Q's to those that are between 3 and 4 have it in orange, when turning orange Q's to red Q's those that are between 1 and 2 have it turn in red.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
What do you want to do if the date in column "B" is prior to today?
0
 

Author Comment

by:Omar Hernandez
Comment Utility
If it is coming to existing of today, have it shown as follow (TODAY)
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
What I'm asking is this. If the day in column B is today then I know that column J should say "(Today)", but what should it say when we refresh tomorrow?
0
 

Author Comment

by:Omar Hernandez
Comment Utility
oh i see, i wanted to create a new question when it got to this additional step, if you don't mind if possible i would appreciate it, if it say the following.

***Considering today date if the following date are shown on the "B" column i would like it to appear on the "J" column the following.
(PASS DUE -3) if on column "B" date is 10/2/16.
(PASS DUE -2) if on column "B" date is 10/3/16.
(PASS DUE -1) If on column "B" date is 10/4/16.

so if it is                                  (TODAY)         Column "B" 10/5/16
tomorrow would be        (PASS DUE -1)    Column "B" 10/5/16
Day after that would be (PASS DUE -2)    Column "B" 10/5/16
Day after that would be (PASS DUE -3)    Column "B" 10/5/16

There is couple of thing that should be done to the project to get it to the way i want, but if i put it on one question it will look like a great deal of work. This is why i am trying to keep every question simple.
0
 
LVL 45

Accepted Solution

by:
Martin Liss earned 500 total points
Comment Utility
Ok, I agree that a new question is called for.

Try the following and see if it fixes the color problem. Note that range J43 is currently blank and will be dealt with in the new question.
28973745c.xlsm
0
 

Author Closing Comment

by:Omar Hernandez
Comment Utility
OMGGGGG, Thanks a mil Martin Liss, you are a true boss when it comes to coding. Can't express enough how much i really appreciate your help and your time.
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
You're welcome and I'm glad I was able to help.

If you expand the “Full Biography” section of my profile you'll find links to some articles I've written that may interest you.

Marty - Microsoft MVP 2009 to 2016
              Experts Exchange MVE 2015
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2015
0
 

Author Comment

by:Omar Hernandez
Comment Utility
Hey Martin Liss, the Information on the "J" column that you have created works great. One thing is that i noticed that the highlight function works, the double click on a cell works, but only for me to use it one time, if i re-click on any cell the following happens.

***Highlight function get's disabled
***Double click function get's disabled

Can we have them available to work in conjunction with updates you have made on this file.
0
 

Author Comment

by:Omar Hernandez
Comment Utility
you can see the glitch i am having on this link
https://www.youtube.com/watch?v=uR37EqXmUrA&feature=youtu.be

Or

Go to YouTube and on search bar copy and paste the following
Problem with clicking, highlight and search column disabled
0
 
LVL 45

Expert Comment

by:Martin Liss
Comment Utility
I'd like a new question for this please.
0
 

Author Comment

by:Omar Hernandez
Comment Utility
No problem friend, you may find it on the this link

https://www.experts-exchange.com/questions/28974603/Fix-Highlighted-and-click-on-cell-to-search-for-specific-column.html

or

search bar
Fix Highlighted and click on cell to search for specific column
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now