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
48 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
  • 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
 

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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

What Security Threats Are You Missing?

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

746 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