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]

Try this. If it doesn't work we'll have to go another route.

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

 

[kevin9999]

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..

Try the following:
Format the cells with the %'s as percentage/no decimal places (which will show your 100% without the decimals)
Add conditional formatting to those cells:
Conditional Formatting / New Rule / Use a formula to determine which cells to format
Enter the formula: =MOD(C1,1)>0 (where C1 is the cell with the %)
Set the format to percentage - 2 decimal places
This is what you get:

Book1
	C
1	100%
2	99.80%
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1:C2	Expression	=MOD(C1,1)>0	text	NO

 

[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.