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

 

[kevin9999]

Or even simpler, make the format for your cells of interest percentage - 2 decimal places - and your conditional formatting formula = if cell equals 100% then no decimal places; like this:

Book1
	C
1	100%
2	99.80%
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C1:C2	Expression	=C1=1	text	NO

 

[edge37]

PRUEBA 2024.xlsm
	AM
14	0.0%
10A (1)

Cell Formulas
Range	Formula
AM14	AM14	=IFERROR((VLOOKUP($AM$9,$B$9:$Z$33,19,FALSE)/100),0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
AM14	Expression	=AM14=100	text	NO

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

Thank youSkybot, it works great. Thank you again, my friend!

 

[edge37]

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

Thank you kevin9999, It worked wonderful. Thank you for your kindness, mate.

 

[kevin9999]

Glad we could help & thanks for the feedback