Solved

Changing amount of letter and color on column "J" when numbers on are being changed in column "I" automatically.

Posted on 2016-09-26
8
64 Views
Last Modified: 2016-09-27
Hello guys i am trying to find out if this is possible, thanks again for your time and help.

On the "J" column have the Letter (Q) display no more than five times with the color of BLUE if on column "I" there is a number gretter than 5
On the "J" column have the Letter (Q) display Color orange if number is three on the "I" column with numbers of (Q) being shown equal to those of "I" column
On the "J" column have the Letter (Q) display Color Red if "I" column display one

q-s.png
completed-9-26-OpEN.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
  • 4
  • 4
8 Comments
 
LVL 35

Expert Comment

by:Kimputer
ID: 41816142
Yes, it's possible with VBA (and even trigger it on column I edits).
I'll have code later (unless someone beats me), since I'm not free at the moment.
0
 

Author Comment

by:Omar Hernandez
ID: 41816258
Thanks for your response Kimputer, forgot to mention to have the Q letters in (BOLD) WITH (SNAP ITC FONT)
0
 
LVL 35

Expert Comment

by:Kimputer
ID: 41816849
The following code should be on the same sheet as where you want the changes to happen:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 9 Then
    If Target.Value > 0 Then
        If Target.Value = 1 Then
            Cells(Target.Row, 10).Value = "Q"
            Cells(Target.Row, 10).Font.Color = -16776961
        ElseIf Target.Value = 2 Then
            Cells(Target.Row, 10).Value = "QQ"
            Cells(Target.Row, 10).Font.ThemeColor = xlThemeColorAccent2
            Cells(Target.Row, 10).Font.TintAndShade = 0
        ElseIf Target.Value = 3 Then
            Cells(Target.Row, 10).Value = "QQQ"
            Cells(Target.Row, 10).Font.ThemeColor = xlThemeColorAccent2
            Cells(Target.Row, 10).Font.TintAndShade = 0
        ElseIf Target.Value = 4 Then
            Cells(Target.Row, 10).Value = "QQQQ"
            Cells(Target.Row, 10).Font.ThemeColor = xlThemeColorAccent2
            Cells(Target.Row, 10).Font.TintAndShade = 0
        ElseIf Target.Value > 4 Then
            Cells(Target.Row, 10).Value = "QQQQQ"
            Cells(Target.Row, 10).Font.Color = -65536
        End If
        Cells(Target.Row, 10).Font.Name = "Snap ITC"
        Cells(Target.Row, 10).Font.FontStyle = "Bold"
        Cells(Target.Row, 10).Font.Size = 8
    End If
End If

End Sub

Open in new window

0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

Author Comment

by:Omar Hernandez
ID: 41817005
Hello there Kimputer, the column where i have it programmed has no module or sheet that i know of i would attach the file here, so that it may be easier for you to suggest another way to have it work.
completed-9-24-OPEN-1.xlsm
0
 
LVL 35

Expert Comment

by:Kimputer
ID: 41817676
Open Excel file, press ALT+F11, navigate to Sheet3(GENERAL), copy & paste code there. Here's some updated code, as well as the excel file attached if you really can't do it:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 9 Then
    If Target.Value > 0 Then
        If Target.Value = 1 Then
            Cells(Target.Row, 10).Value = "Q"
            Cells(Target.Row, 10).Font.Color = -16776961
        ElseIf Target.Value = 2 Then
            Cells(Target.Row, 10).Value = "QQ"
            Cells(Target.Row, 10).Font.Color = 26367
        ElseIf Target.Value = 3 Then
            Cells(Target.Row, 10).Value = "QQQ"
            Cells(Target.Row, 10).Font.Color = 26367
        ElseIf Target.Value = 4 Then
            Cells(Target.Row, 10).Value = "QQQQ"
            Cells(Target.Row, 10).Font.Color = 26367
        ElseIf Target.Value > 4 Then
            Cells(Target.Row, 10).Value = "QQQQQ"
            Cells(Target.Row, 10).Font.Color = -65536
        End If
        Cells(Target.Row, 10).Font.Name = "Snap ITC"
        Cells(Target.Row, 10).Font.FontStyle = "Bold"
        Cells(Target.Row, 10).Font.Size = 8
    End If
    If Target.Value = "" Then
        Cells(Target.Row, 10).Value = ""
    End If
End If

End Sub

Open in new window

completed-9-24-OPEN-1.xlsm
0
 

Author Comment

by:Omar Hernandez
ID: 41817735
Thanks for your response, i see now what was the issue. It works great, but i forgot to mention that the "I"column has a code that goes like this (=IF(B6<>"",B6-TODAY(),"")).

***Any way we can make it work with this code or if you have a better suggestion please let me know so that i may have the "I" column calculating the present day and how many days would be left when calculating it with the "B" column.
***also with the "J" column to have the (Q letter) representing the amount of days left.
0
 
LVL 35

Accepted Solution

by:
Kimputer earned 500 total points
ID: 41817786
It's still possible, triggers will be ONLY column 2 now.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim eval As Long
If Target.Column = 2 Then
    eval = Cells(Target.Row, 2).Value - Date
    If eval > 0 Then
        If eval = 1 Then
            Cells(Target.Row, 10).Value = "Q"
            Cells(Target.Row, 10).Font.Color = -16776961
        ElseIf eval = 2 Then
            Cells(Target.Row, 10).Value = "QQ"
            Cells(Target.Row, 10).Font.Color = 26367
        ElseIf eval = 3 Then
            Cells(Target.Row, 10).Value = "QQQ"
            Cells(Target.Row, 10).Font.Color = 26367
        ElseIf eval = 4 Then
            Cells(Target.Row, 10).Value = "QQQQ"
            Cells(Target.Row, 10).Font.Color = 26367
        ElseIf eval > 4 Then
            Cells(Target.Row, 10).Value = "QQQQQ"
            Cells(Target.Row, 10).Font.Color = -65536
        End If
        Cells(Target.Row, 10).Font.Name = "Snap ITC"
        Cells(Target.Row, 10).Font.FontStyle = "Bold"
        Cells(Target.Row, 10).Font.Size = 8
    Else
        Cells(Target.Row, 10).Value = ""
    End If
    If Cells(Target.Row, 2) = Empty Then
        Cells(Target.Row, 10).Value = ""
    End If
End If

End Sub

Open in new window


Since code is now dependent on other input than yourself, need to know default action if value is < 0
(For now, it's just emptying the cell).
0
 

Author Closing Comment

by:Omar Hernandez
ID: 41817817
This new modification works superb, thanks a mil kimputer. Just what i was looking for :)
0

Featured Post

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

739 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