Alignment

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

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 wrote:
>
> 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

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?




On Tue, 21 Jun 2005 13:21:17 -0500, Dave Peterson
<ec35720@netscapeXSPAM.com> wrote:

>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 wrote:
>>
>> 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

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 wrote:
>
> 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?
>
> On Tue, 21 Jun 2005 13:21:17 -0500, Dave Peterson
> <ec35720@netscapeXSPAM.com> wrote:
>
> >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 wrote:
> >>
> >> 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

Thanks again

Ed English


On Tue, 21 Jun 2005 17:33:38 -0500, Dave Peterson
<ec35720@netscapeXSPAM.com> wrote:

>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 wrote:
>>
>> 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?
>>
>> On Tue, 21 Jun 2005 13:21:17 -0500, Dave Peterson
>> <ec35720@netscapeXSPAM.com> wrote:
>>
>> >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 wrote:
>> >>
>> >> 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