Clash Royale CLAN TAG#URR8PPP

EXCEL VBA: Format existing numeric cells based on value range

Am trying to run VBA on certain columns (ex: M, N, U, V...) to format the values based on their range.

I currently have:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("U:W")) Is Nothing Then
If Target.Value < -1000000 Then
Target.NumberFormat = "#,###.0,, ""M"""
ElseIf Target.Value <= -1000 Then
Target.NumberFormat = "#,###.0, ""K"""
ElseIf Target.Value < 1000 Then
Target.NumberFormat = "### """""
ElseIf Target.Value < 1000000 Then
Target.NumberFormat = "#,###.0, ""K"""
ElseIf Target.Value < 1000000000 Then
Target.NumberFormat = "#,###.0,, ""M"""
ElseIf Target.Value < 1000000000000# Then
Target.NumberFormat = "#,###.0,,, ""B"""

End If
End If
End Sub


Unfortunately, this isn't working on values already entered. However, if I click in each cell and then hit return, if formats correctly.

QUESTION: How would I go about formatting the values that are already there?

Thank you

2 Answers
2

Looks like the Target is only the range that changed:

https://msdn.microsoft.com/en-us/vba/excel-vba/articles/worksheet-change-event-excel

Target

You'll probably have to write a sub that will loop through the other data, or just "edit" the cells once and the macro will work going forward.

EDIT:

How about looping through the columns and calling your function?:

Sub OneTimeLoop()
Dim rng as Range, cell as Range

set rng = Range("U:V")

For Each cell in rng
Worksheet_Change cell
Next cell
End Sub


Range("U1:V1000")

I think, you need Precedents property, which will retrieve all cells a current cell is dependent upon.

Precedents

Say, you have:

Then the following code will show $A$1:

$A$1

Sub F()
MsgBox Range("A2").Precedents(1).Address
End Sub


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.