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
59 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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
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…

813 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

11 Experts available now in Live!

Get 1:1 Help Now