Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 69
  • Last Modified:

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

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
Omar Hernandez
Asked:
Omar Hernandez
  • 17
  • 13
1 Solution
 
Martin LissOlder than dirtCommented:
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
 
Omar HernandezAuthor Commented:
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
 
Omar HernandezAuthor Commented:
Is there another place i can update the video clip Martin so that you may see where i need help
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Omar HernandezAuthor Commented:
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
 
Martin LissOlder than dirtCommented:
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
 
Omar HernandezAuthor Commented:
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
 
Omar HernandezAuthor Commented:
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
 
Martin LissOlder than dirtCommented:
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
 
Omar HernandezAuthor Commented:
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
 
Martin LissOlder than dirtCommented:
You've got a lot going on in your workbook:)

Try this version.
28973745a.xlsm
0
 
Omar HernandezAuthor Commented:
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
 
Martin LissOlder than dirtCommented:
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
 
Omar HernandezAuthor Commented:
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
 
Martin LissOlder than dirtCommented:
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
 
Omar HernandezAuthor Commented:
tried it, No luck my friend
0
 
Martin LissOlder than dirtCommented:
Please attach your current workbook.
0
 
Omar HernandezAuthor Commented:
Hello there Martin liss, here you go friend
28973745b.xlsm
0
 
Martin LissOlder than dirtCommented:
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
 
Omar HernandezAuthor Commented:
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
 
Martin LissOlder than dirtCommented:
What do you want to do if the date in column "B" is prior to today?
0
 
Omar HernandezAuthor Commented:
If it is coming to existing of today, have it shown as follow (TODAY)
0
 
Martin LissOlder than dirtCommented:
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
 
Omar HernandezAuthor Commented:
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
 
Martin LissOlder than dirtCommented:
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
 
Omar HernandezAuthor Commented:
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
 
Martin LissOlder than dirtCommented:
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
 
Omar HernandezAuthor Commented:
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
 
Omar HernandezAuthor Commented:
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
 
Martin LissOlder than dirtCommented:
I'd like a new question for this please.
0
 
Omar HernandezAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 17
  • 13
Tackle projects and never again get stuck behind a technical roadblock.
Join Now