Excel reversing cell value?

[allenz0rz]

Hi guys,

I have a table, datas from B2:M13. Datas are a 1 digit number, a "-" and another 1 digit number. For example, E3 is "7-3". Here's what I'd like to do:
When I click on any cell for this table, the content of every cell in the active column reverses itself. In previous example, if I click on any E, everything in column E would reverse, meaning that E3 would show "3-7" instead of "7-3". When clicking into another column, content would have to come back to the original settings.

Thank you

[mbarron]

Make sure your B2:M13 range is formatted as Text otherwise the changed cells will become dates. The following will switch the numbers around when you click within the table. If you click outside of the table the last selected column will be reversed. If you click in the same column, nothing happens to the numbers (they do change, but then are changed back)

Assign the following to the the selection change event (right click on the tab and choose View Code..., then paste the following macro):
[vba]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static lCol As Integer, cCell As Range
If Not Intersect(Target, Range("B2:M13")) Is Nothing Then
Application.EnableEvents = False
For Each cCell In Range(Cells(2, Target.Column), Cells(13, Target.Column))
cCell.Value = CStr(Right(cCell, 1) & "-" & Left(cCell, 1))
Next
If lCol <> 0 Then
For Each cCell In Range(Cells(2, lCol), Cells(13, lCol))
cCell.Value = CStr(Right(cCell, 1) & "-" & Left(cCell, 1))
Next
End If
lCol = Target.Column
Application.EnableEvents = True
Else
Application.EnableEvents = False
If lCol <> 0 Then
For Each cCell In Range(Cells(2, lCol), Cells(13, lCol))
cCell.Value = CStr(Right(cCell, 1) & "-" & Left(cCell, 1))
Next
lCol = 0
End If
Application.EnableEvents = True
End If
End Sub
[/vba]

[allenz0rz]

Make sure your B2:M13 range is formatted as Text otherwise the changed cells will become dates. The following will switch the numbers around when you click within the table. If you click outside of the table the last selected column will be reversed. If you click in the same column, nothing happens to the numbers (they do change, but then are changed back)

Assign the following to the the selection change event (right click on the tab and choose View Code..., then paste the following macro):
[vba]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static lCol As Integer, cCell As Range
If Not Intersect(Target, Range("B2:M13")) Is Nothing Then
Application.EnableEvents = False
For Each cCell In Range(Cells(2, Target.Column), Cells(13, Target.Column))
cCell.Value = CStr(Right(cCell, 1) & "-" & Left(cCell, 1))
Next
If lCol <> 0 Then
For Each cCell In Range(Cells(2, lCol), Cells(13, lCol))
cCell.Value = CStr(Right(cCell, 1) & "-" & Left(cCell, 1))
Next
End If
lCol = Target.Column
Application.EnableEvents = True
Else
Application.EnableEvents = False
If lCol <> 0 Then
For Each cCell In Range(Cells(2, lCol), Cells(13, lCol))
cCell.Value = CStr(Right(cCell, 1) & "-" & Left(cCell, 1))
Next
lCol = 0
End If
Application.EnableEvents = True
End If
End Sub
[/vba]

Great thank you very much . Now one last thing:
I would need to display the letter "L" in column N everytime "new" first digit is lower than "new" second digit, in the corresponding row. Letter "W" has to be displayed when the opposite occurs, and "D" then they're equal. Those letters would have to be erased and replaced by the new value, depending on which cell is selected. In previous example, when clicking E5, it now displays "3-7" in E3, and it should display "L" in N3. Thank you buddies. I would love to be better at coding lol