How to remove decimal numbers out of only 100%

[edge37]

Greeting! I have this question: I have a cell in which I need to show averages of grades, that includes 2 decimals, as percents, but I would like to format the cell for, when I have a 100% average, all decimals are removed, the rest of numbers will keep their decimals, EXCEPT 100%, which I want it to show without any decimals..
I'm using a VBA code and a formula for showing the averages in just one cell when I click a student's name:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Range("AM9").Value = ActiveCell.Value
End Sub

=CELL("address")

In Format Cell>Number>Custom, I use 0.00%;;""
In the cell I use the formula:

=IFERROR((VLOOKUP($AM$9,$B$9:$Z$33,19,FALSE)/100),0)

Is this possible? Thanks for your help

Please advice if I have to send anything else to fully explain myself. The image illustrates the problem.

 

[Skyybot]

In your VBA code; which cell is ActiveCell?

 

[edge37]

In your VBA code; which cell is ActiveCell?

It's AM9

 

[Skyybot]

It's AM9

I am confused. That code will literally just replace the cell value with the cell value. Like saying 3 = 3. Are you looking for something like this?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = Range("AM9").Address Then
    If Target.Value = 1 Then
        Target.NumberFormat = "0%"
    End If
End If
End Sub

 

[Scott Huish]

If those are whole numbers in those cells, why not use a different # format:
[=100]0\%;General

 

[edge37]

If those are whole numbers in those cells, why not use a different # format:
[=100]0\%;General

This # format returns the average fine, but the % sign is lost in all average values except with 100, only when the average is 100 it appears as 100% (with % sign). I need all average values to show with the % sign as well. Thank you.

 

[edge37]

I am confused. That code will literally just replace the cell value with the cell value. Like saying 3 = 3. Are you looking for something like this?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = Range("AM9").Address Then
    If Target.Value = 1 Then
        Target.NumberFormat = "0%"
    End If
End If
End Sub

Thank you. Yes, the code is just for showing what I want in that particular cell, I use it along with =CELL("address") for many desired effects in my worksheet as well. Anyway, I try to substitute my code with yours, but I notice that all my other actions stop working and the problem that I want to solve still is not solved. In my unexperienced mind, I thought that solving the problem I'm having didn't have anything to do with that particular code, but again, how could I know. Thank you again.

 

[edge37]

Right now I have solved the problem by using, in another cell, the formula:

=IF(AM14=100.00%,"100%",AM14)

with the # format: 0.00%;;"" and redirecting information to that cell to have the effect I'm looking for, but I still would like to know if the former cell I could could have the effect that I was looking for. I thank you all very much for your kind help.

 

[Skyybot]

I think so. Try this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("AM9").Value = ActiveCell.Value
If Range("AM9") = 1 Then
        Range("AM9").NumberFormat = "0%"
End If
End Sub

 

[edge37]

I think so. Try this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("AM9").Value = ActiveCell.Value
If Range("AM9") = 1 Then
        Range("AM9").NumberFormat = "0%"
End If
End Sub

Thank you again Skybot, but again it's not working for me, the 100% still shows 100.0%. I included an image to illustrate the worksheet using your VBA code already. Thanks again..