Alignment

[ED]

Code please to align wording in a cell such as:

If cell E1 is greater than 0 the wording in cell is aligned right,
other wise wording in cell is aligned left.

Thanks in advance


Ed English

 

[Dave Peterson]

Since you're writing about wording, are you using e1 to control another cell?

If yes, how about something like:

Option Explicit
Sub testme01()

Dim myCell As Range
Dim myRng As Range

With ActiveSheet
Set myCell = .Range("e1")
Set myRng = .Range("f1")
End With

If myCell.Value > 0 Then
myRng.HorizontalAlignment = xlRight
Else
myRng.HorizontalAlignment = xlLeft
End If

End Sub

 

[ED]

Thanks for the code..

I was not too clear in my original question, I'm working up a spread
sheet where I want the wording in column C cells to be aligned right
if there is a plus number value in the corresponding E cell, otherwise
the wording in column C is aligned left.

I thought that putting the following in C1:

=If(E1>0,myRng.HorizontalAlignment = xlRight,myRng.HorizontalAlignment
= xlLeft)


would set the alignment right in C1, then the the wording I put in C1
would be aligned right.

It doesn't seem to work. Any comments?

 

[Dave Peterson]

To change the alignment, you're gonna need a macro. This doesn't go on the
worksheet itself.

This will adjust when you change something in column E.

Rightclick on the worksheet tab that should have this behavior. Select View
Code and paste this in the code window:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRng As Range
Dim myCell As Range

Set myRng = Intersect(Target, Me.Range("e:e"))

If myRng Is Nothing Then Exit Sub

On Error Resume Next
For Each myCell In myRng.Cells
If myCell.Value > 0 Then
Me.Cells(myCell.Row, "C").HorizontalAlignment = xlRight
Else
Me.Cells(myCell.Row, "C").HorizontalAlignment = xlLeft
End If
Next myCell
On Error Goto 0

End Sub

 

[ED]

Thanks again

Ed English

To change the alignment, you're gonna need a macro. This doesn't go on the
worksheet itself.

This will adjust when you change something in column E.

Rightclick on the worksheet tab that should have this behavior. Select View
Code and paste this in the code window:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRng As Range
Dim myCell As Range

Set myRng = Intersect(Target, Me.Range("e:e"))

If myRng Is Nothing Then Exit Sub

On Error Resume Next
For Each myCell In myRng.Cells
If myCell.Value > 0 Then
Me.Cells(myCell.Row, "C").HorizontalAlignment = xlRight
Else
Me.Cells(myCell.Row, "C").HorizontalAlignment = xlLeft
End If
Next myCell
On Error Goto 0

End Sub