Using base format "%" and conditional format "$"

[Lennane]

Thanks to all that post responses to questions on this forum. I have successfully used many over the last six months.

My first post now.

I can't find a solution to my current problem. Did search but no specific titles indicating that the issue was covered previously. Apologies if it has.

I am formating some cells with a base % format. To enter 10% I enter "10", not ".1". Makes % entry very simple.

However the cells reference a data validation list with a number of % options that also have one option for a $ value. So I have a conditional format that applies a currency format in that case.

The issue I have is that when I enter $'s into the cell I have to add the cents without a decimal point or else the base format is applied. That is, for $100,000, I want to enter 100000 but excel expresses that entry as $1,000 after applying my conditional format. The cell value shows 100000%.

Any suggestions? Thanks in advance.

 

[Lennane]

Thanks Jerry.

Not quite as simple as that. Refer below:

<TABLE style="WIDTH: 496pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=660><COLGROUP><COL style="WIDTH: 124pt; mso-width-source: userset; mso-width-alt: 5280" span=4 width=165><TBODY><TR style="HEIGHT: 21pt" height=28><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 248pt; HEIGHT: 21pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl64 height=28 width=330 colSpan=2>Data Validation List</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 248pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: black 0.5pt solid" class=xl64 width=330 colSpan=2>Corresponding User Data</TD></TR><TR style="HEIGHT: 18.75pt" height=25><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 18.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=25>Sheet Name</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Cell Reference</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Sheet Name</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66>Cell Reference</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=21>Title & Preferences</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>L21</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Project</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>G29</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=21>Title & Preferences</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>L22</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Project</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>G24</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=21>Title & Preferences</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>L23</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Project</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>G25</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=21>Title & Preferences</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>L25</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Project</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>G33</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=21>Title & Preferences</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>L26</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Project</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>G34</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=21>Title & Preferences</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>L27</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Project</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>G35</TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=21>Title & Preferences</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>L28</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>Project</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>G36</TD></TR></TBODY></TABLE>

 

[Jerry Sullivan]

If you are referring to the multiple cells available for DV list selection and entering the number value, we can handle that as step 2.

Try the example using just Cell A2 for DV dropdown and B2 for values and let me know if the format and behavior are what you want.

Also, I'm not following from your screen shot how the 4 columns shown relate to the 2 columns we have been discussing. Please clarify that.

 

[Lennane]

Yes, that works beautifully. I will change the % format to 0.00%, but otherwise perfect.

Its not a screenshot. Its a summary. There are two sheets. The DV selection occurs in the cells on the sheet "Title & Preferences". So there are seven cells on that sheet (L21-L23, L25-L28) that the user can select an option from. Each of those cells links back to a named list on another sheet.

The user data is entered on the sheet "Project" and the cell references relate to the adjacent DV cell reference. So L21 and G29 relate.

Note the relationships are not contiguous.

Appreciate the great help Jerry.

 

[Lennane]

I am afraid this is beyond my comprehension Jerry.

Apologies if I am stretching the friendship, but how do I now execute step 2.

 

[Jerry Sullivan]

I am afraid this is beyond my comprehension Jerry.

Apologies if I am stretching the friendship, but how do I now execute step 2.

Hi Lennane,

The apologies are all mine - I lost track of this as an unresolved thread.

You can copy this revision into your "Project" sheet code.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sDVal_Addresses As String, sUser_Addresses As String
    Dim lIdx As Long
    sDVal_Addresses = "$L$21,$L$22,$L$23,$L$25,$L$26,$L$27,$L$28"
    sUser_Addresses = "$G$29,$G$24,$G$25,$G$33,$G$34,$G$35,$G$36"
    
    If Intersect(Target, Range(sDVal_Addresses)) Is Nothing _
        Or Target.Cells.Count > 1 Then Exit Sub

    On Error GoTo CleanUp
    Application.EnableEvents = False
    
    '---get item index in string lists
    lIdx = WorksheetFunction.Match(Target.Address, _
        Split(sDVal_Addresses, ","), 0)
      
    With Sheets("Project").Range(Split(sUser_Addresses, ",")(lIdx - 1))
        If InStr(Target, "$") Then
            If InStr(.NumberFormat, "%") > 0 And _
                IsNumeric(.Value) Then _
                .Value = .Value * 100
            .NumberFormat = "$#,##0"
        Else
            If InStr(.NumberFormat, "%") = 0 And _
                IsNumeric(.Value) Then _
                .Value = .Value * 0.01
            .NumberFormat = "0%"
        End If
    End With
CleanUp:
    Application.EnableEvents = True
End Sub

Depending on how often you edit your mapping, you might consider having the mapping as a table in your worksheet instead hard-coded into the VBA code.

Just let me know if you would like to help with that change.

 

[Lennane]

Hi Jerry,

Thank you.

If I insert into "Project" sheet code it will search only that page for each cell reference. So should I put in "Title & Preferences" where there is no reference to that sheet in the code? I'll try that first.

Regards, Brett

 

[Jerry Sullivan]

Hi Jerry,

Thank you.

If I insert into "Project" sheet code it will search only that page for each cell reference. So should I put in "Title & Preferences" where there is no reference to that sheet in the code? I'll try that first.

Regards, Brett

You're right Brett. It should be the sheet with the DV Cells which is T&P.

 

[Lennane]

That's pretty much the extent of my VBA capabilities Jerry.

 

[Jerry Sullivan]

That's pretty much the extent of my VBA capabilities Jerry.

Did it work?

 

[Lennane]

Jerry......

Yes, it's brilliant! Exactly what I needed.

Extensive testing to come but it looks perfect.

Thank you.