Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
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
Medium Priority
?
69 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 36

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 36

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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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 36

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 36

Accepted Solution

by:
Kimputer earned 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

704 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