?
Solved

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

Posted on 2016-10-01
30
Medium Priority
?
57 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 17
  • 13
30 Comments
 
LVL 49

Expert Comment

by:Martin Liss
ID: 41825079
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
ID: 41825087
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
ID: 41825088
Is there another place i can update the video clip Martin so that you may see where i need help
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Omar Hernandez
ID: 41825094
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 49

Expert Comment

by:Martin Liss
ID: 41825417
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
ID: 41825431
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
ID: 41825440
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 49

Expert Comment

by:Martin Liss
ID: 41826642
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
ID: 41827067
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 49

Expert Comment

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

Try this version.
28973745a.xlsm
0
 

Author Comment

by:Omar Hernandez
ID: 41827389
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 49

Expert Comment

by:Martin Liss
ID: 41827920
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
ID: 41828402
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 49

Expert Comment

by:Martin Liss
ID: 41828614
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
ID: 41828814
tried it, No luck my friend
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 41828869
Please attach your current workbook.
0
 

Author Comment

by:Omar Hernandez
ID: 41829040
Hello there Martin liss, here you go friend
28973745b.xlsm
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 41829064
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
ID: 41829126
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 49

Expert Comment

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

Author Comment

by:Omar Hernandez
ID: 41830005
If it is coming to existing of today, have it shown as follow (TODAY)
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 41830234
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
ID: 41830484
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 49

Accepted Solution

by:
Martin Liss earned 2000 total points
ID: 41830516
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
ID: 41830525
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 49

Expert Comment

by:Martin Liss
ID: 41830539
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
ID: 41830576
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
ID: 41830635
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 49

Expert Comment

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

Author Comment

by:Omar Hernandez
ID: 41830736
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

752 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