I'm trying to find code that will change the font color or interior color in
a cell when the cell is clicked once. Then when the color changes I need to
recalculate a formula from a different cell in the same row based on which
cell was chosen and had the color change.

I was trying the following code as a start but I am getting an "End If
without a Block If" error.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("L2:M500")) Is Nothing Then
Target.Interior.ColorIndex = 5
End If
End Sub

Any help is greatly appreciated.

Nothing wrong with what you present.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"aileen" <(E-Mail Removed)> wrote in message
news:9CE0BA13-A8F5-4970-ABB3-(E-Mail Removed)...
> I'm trying to find code that will change the font color or interior color
> in
> a cell when the cell is clicked once. Then when the color changes I need
> to
> recalculate a formula from a different cell in the same row based on which
> cell was chosen and had the color change.
>
> I was trying the following code as a start but I am getting an "End If
> without a Block If" error.
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> If Not Intersect(Target, Range("L2:M500")) Is Nothing Then
> Target.Interior.ColorIndex = 5
> End If
> End Sub
>
> Any help is greatly appreciated.

My bet is that you had this:

If Not Intersect(...)) Is Nothing Then Target.Interior.ColorIndex = 5

All on one line in your code.

When you pasted it into the message, the line got wrapped nicely:

If Not Intersect(Target, Range("L2:M500")) Is Nothing Then
Target.Interior.ColorIndex = 5
End if

And fixed the problem!

So hit enter after that Then and you'll be fine.

aileen wrote:
>
> I'm trying to find code that will change the font color or interior color in
> a cell when the cell is clicked once. Then when the color changes I need to
> recalculate a formula from a different cell in the same row based on which
> cell was chosen and had the color change.
>
> I was trying the following code as a start but I am getting an "End If
> without a Block If" error.
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> If Not Intersect(Target, Range("L2:M500")) Is Nothing Then
> Target.Interior.ColorIndex = 5
> End If
> End Sub
>
> Any help is greatly appreciated.

--

Dave Peterson

Don,

I was able to get the cell color change to work, but I'm still working on
having a formula in another cell in the same row change based on the cell I
choose to color.

Any ideas?

Thanks,
Aileen

"Don Guillett" wrote:

> Nothing wrong with what you present.
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "aileen" <(E-Mail Removed)> wrote in message
> news:9CE0BA13-A8F5-4970-ABB3-(E-Mail Removed)...
> > I'm trying to find code that will change the font color or interior color
> > in
> > a cell when the cell is clicked once. Then when the color changes I need
> > to
> > recalculate a formula from a different cell in the same row based on which
> > cell was chosen and had the color change.
> >
> > I was trying the following code as a start but I am getting an "End If
> > without a Block If" error.
> >
> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > If Not Intersect(Target, Range("L2:M500")) Is Nothing Then
> > Target.Interior.ColorIndex = 5
> > End If
> > End Sub
> >
> > Any help is greatly appreciated.
>
> .
>

I should be more specific. The formula is simple it's the product of 3
cells, 2 of which are fixed and the last one should become whichever cell
I've clicked.

Cells(j, "J") = Cells(j, "E") * Cells(j, "I") * highlighted cell

Is this possible to do?


"Don Guillett" wrote:

> Nothing wrong with what you present.
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "aileen" <(E-Mail Removed)> wrote in message
> news:9CE0BA13-A8F5-4970-ABB3-(E-Mail Removed)...
> > I'm trying to find code that will change the font color or interior color
> > in
> > a cell when the cell is clicked once. Then when the color changes I need
> > to
> > recalculate a formula from a different cell in the same row based on which
> > cell was chosen and had the color change.
> >
> > I was trying the following code as a start but I am getting an "End If
> > without a Block If" error.
> >
> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > If Not Intersect(Target, Range("L2:M500")) Is Nothing Then
> > Target.Interior.ColorIndex = 5
> > End If
> > End Sub
> >
> > Any help is greatly appreciated.
>
> .
>

Yes that was the problem. Thanks for your input.


"Dave Peterson" wrote:

> My bet is that you had this:
>
> If Not Intersect(...)) Is Nothing Then Target.Interior.ColorIndex = 5
>
> All on one line in your code.
>
> When you pasted it into the message, the line got wrapped nicely:
>
> If Not Intersect(Target, Range("L2:M500")) Is Nothing Then
> Target.Interior.ColorIndex = 5
> End if
>
> And fixed the problem!
>
> So hit enter after that Then and you'll be fine.
>
> aileen wrote:
> >
> > I'm trying to find code that will change the font color or interior color in
> > a cell when the cell is clicked once. Then when the color changes I need to
> > recalculate a formula from a different cell in the same row based on which
> > cell was chosen and had the color change.
> >
> > I was trying the following code as a start but I am getting an "End If
> > without a Block If" error.
> >
> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > If Not Intersect(Target, Range("L2:M500")) Is Nothing Then
> > Target.Interior.ColorIndex = 5
> > End If
> > End Sub
> >
> > Any help is greatly appreciated.
>
> --
>
> Dave Peterson
> .
>

Untested.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("L2:M500")) Is Nothing Then
tr = Target.Row
Target.Interior.ColorIndex = 5
Cells(tr, "J") = Cells(tr, "e") * Cells(tr, "i") * Target
End If
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"aileen" <(E-Mail Removed)> wrote in message
news:95B38B8A-012D-4D38-B28F-(E-Mail Removed)...
>I should be more specific. The formula is simple it's the product of 3
> cells, 2 of which are fixed and the last one should become whichever cell
> I've clicked.
>
> Cells(j, "J") = Cells(j, "E") * Cells(j, "I") * highlighted cell
>
> Is this possible to do?
>
>
> "Don Guillett" wrote:
>
>> Nothing wrong with what you present.
>>
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> (E-Mail Removed)
>> "aileen" <(E-Mail Removed)> wrote in message
>> news:9CE0BA13-A8F5-4970-ABB3-(E-Mail Removed)...
>> > I'm trying to find code that will change the font color or interior
>> > color
>> > in
>> > a cell when the cell is clicked once. Then when the color changes I
>> > need
>> > to
>> > recalculate a formula from a different cell in the same row based on
>> > which
>> > cell was chosen and had the color change.
>> >
>> > I was trying the following code as a start but I am getting an "End If
>> > without a Block If" error.
>> >
>> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>> > If Not Intersect(Target, Range("L2:M500")) Is Nothing Then
>> > Target.Interior.ColorIndex = 5
>> > End If
>> > End Sub
>> >
>> > Any help is greatly appreciated.
>>
>> .
>>

Your code works great, but then my code to toggle colors in a clicked cell
stops allowing the cell to go back to white once I have clicked a cell after
it's already been clicked once and turned red. My code is below. Please let
me know what I am doing wrong.

If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("F:F,H:H,L:N")) Is Nothing Then Exit Sub
If Target.Interior.ColorIndex = xlNone Then
Target.Interior.ColorIndex = 3
Else
If Target.Interior.ColorIndex = 3 Then
Target.Interior.ColorIndex = xlNone
End If
End If

If Not Intersect(Target, Range("F:F,H:H,L:N")) Is Nothing Then
tr = Target.Row
Target.Interior.ColorIndex = 3
Cells(tr, "J") = Cells(tr, "O") * Cells(tr, "I") * Target
End If

Thanks so much for all your help.

"Don Guillett" wrote:

> Untested.
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> If Target.Count > 1 Then Exit Sub
> If Not Intersect(Target, Range("L2:M500")) Is Nothing Then
> tr = Target.Row
> Target.Interior.ColorIndex = 5
> Cells(tr, "J") = Cells(tr, "e") * Cells(tr, "i") * Target
> End If
> End Sub
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "aileen" <(E-Mail Removed)> wrote in message
> news:95B38B8A-012D-4D38-B28F-(E-Mail Removed)...
> >I should be more specific. The formula is simple it's the product of 3
> > cells, 2 of which are fixed and the last one should become whichever cell
> > I've clicked.
> >
> > Cells(j, "J") = Cells(j, "E") * Cells(j, "I") * highlighted cell
> >
> > Is this possible to do?
> >
> >
> > "Don Guillett" wrote:
> >
> >> Nothing wrong with what you present.
> >>
> >> --
> >> Don Guillett
> >> Microsoft MVP Excel
> >> SalesAid Software
> >> (E-Mail Removed)
> >> "aileen" <(E-Mail Removed)> wrote in message
> >> news:9CE0BA13-A8F5-4970-ABB3-(E-Mail Removed)...
> >> > I'm trying to find code that will change the font color or interior
> >> > color
> >> > in
> >> > a cell when the cell is clicked once. Then when the color changes I
> >> > need
> >> > to
> >> > recalculate a formula from a different cell in the same row based on
> >> > which
> >> > cell was chosen and had the color change.
> >> >
> >> > I was trying the following code as a start but I am getting an "End If
> >> > without a Block If" error.
> >> >
> >> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> >> > If Not Intersect(Target, Range("L2:M500")) Is Nothing Then
> >> > Target.Interior.ColorIndex = 5
> >> > End If
> >> > End Sub
> >> >
> >> > Any help is greatly appreciated.
> >>
> >> .
> >>
>
> .
>

Nevermind, I got your code to work by incorporating into the other code as
such:

If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("F:F,H:H,L:N")) Is Nothing Then Exit Sub
If Target.Interior.ColorIndex = xlNone Then
Target.Interior.ColorIndex = 3
tr = Target.Row
Cells(tr, "J") = Cells(tr, "O") * Cells(tr, "I") * Target
Else
If Target.Interior.ColorIndex = 3 Then
Target.Interior.ColorIndex = xlNone
End If
End If

Thanks again for all your help.


"Don Guillett" wrote:

> Untested.
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> If Target.Count > 1 Then Exit Sub
> If Not Intersect(Target, Range("L2:M500")) Is Nothing Then
> tr = Target.Row
> Target.Interior.ColorIndex = 5
> Cells(tr, "J") = Cells(tr, "e") * Cells(tr, "i") * Target
> End If
> End Sub
>
> --
> Don Guillett
> Microsoft MVP Excel
> SalesAid Software
> (E-Mail Removed)
> "aileen" <(E-Mail Removed)> wrote in message
> news:95B38B8A-012D-4D38-B28F-(E-Mail Removed)...
> >I should be more specific. The formula is simple it's the product of 3
> > cells, 2 of which are fixed and the last one should become whichever cell
> > I've clicked.
> >
> > Cells(j, "J") = Cells(j, "E") * Cells(j, "I") * highlighted cell
> >
> > Is this possible to do?
> >
> >
> > "Don Guillett" wrote:
> >
> >> Nothing wrong with what you present.
> >>
> >> --
> >> Don Guillett
> >> Microsoft MVP Excel
> >> SalesAid Software
> >> (E-Mail Removed)
> >> "aileen" <(E-Mail Removed)> wrote in message
> >> news:9CE0BA13-A8F5-4970-ABB3-(E-Mail Removed)...
> >> > I'm trying to find code that will change the font color or interior
> >> > color
> >> > in
> >> > a cell when the cell is clicked once. Then when the color changes I
> >> > need
> >> > to
> >> > recalculate a formula from a different cell in the same row based on
> >> > which
> >> > cell was chosen and had the color change.
> >> >
> >> > I was trying the following code as a start but I am getting an "End If
> >> > without a Block If" error.
> >> >
> >> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> >> > If Not Intersect(Target, Range("L2:M500")) Is Nothing Then
> >> > Target.Interior.ColorIndex = 5
> >> > End If
> >> > End Sub
> >> >
> >> > Any help is greatly appreciated.
> >>
> >> .
> >>
>
> .
>

Glad to help

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"aileen" <(E-Mail Removed)> wrote in message
news4FCE7BD-129F-4E67-96C5-(E-Mail Removed)...
> Nevermind, I got your code to work by incorporating into the other code as
> such:
>
> If Target.Count > 1 Then Exit Sub
> If Intersect(Target, Range("F:F,H:H,L:N")) Is Nothing Then Exit Sub
> If Target.Interior.ColorIndex = xlNone Then
> Target.Interior.ColorIndex = 3
> tr = Target.Row
> Cells(tr, "J") = Cells(tr, "O") * Cells(tr, "I") * Target
> Else
> If Target.Interior.ColorIndex = 3 Then
> Target.Interior.ColorIndex = xlNone
> End If
> End If
>
> Thanks again for all your help.
>
>
> "Don Guillett" wrote:
>
>> Untested.
>>
>> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>> If Target.Count > 1 Then Exit Sub
>> If Not Intersect(Target, Range("L2:M500")) Is Nothing Then
>> tr = Target.Row
>> Target.Interior.ColorIndex = 5
>> Cells(tr, "J") = Cells(tr, "e") * Cells(tr, "i") * Target
>> End If
>> End Sub
>>
>> --
>> Don Guillett
>> Microsoft MVP Excel
>> SalesAid Software
>> (E-Mail Removed)
>> "aileen" <(E-Mail Removed)> wrote in message
>> news:95B38B8A-012D-4D38-B28F-(E-Mail Removed)...
>> >I should be more specific. The formula is simple it's the product of 3
>> > cells, 2 of which are fixed and the last one should become whichever
>> > cell
>> > I've clicked.
>> >
>> > Cells(j, "J") = Cells(j, "E") * Cells(j, "I") * highlighted cell
>> >
>> > Is this possible to do?
>> >
>> >
>> > "Don Guillett" wrote:
>> >
>> >> Nothing wrong with what you present.
>> >>
>> >> --
>> >> Don Guillett
>> >> Microsoft MVP Excel
>> >> SalesAid Software
>> >> (E-Mail Removed)
>> >> "aileen" <(E-Mail Removed)> wrote in message
>> >> news:9CE0BA13-A8F5-4970-ABB3-(E-Mail Removed)...
>> >> > I'm trying to find code that will change the font color or interior
>> >> > color
>> >> > in
>> >> > a cell when the cell is clicked once. Then when the color changes I
>> >> > need
>> >> > to
>> >> > recalculate a formula from a different cell in the same row based on
>> >> > which
>> >> > cell was chosen and had the color change.
>> >> >
>> >> > I was trying the following code as a start but I am getting an "End
>> >> > If
>> >> > without a Block If" error.
>> >> >
>> >> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>> >> > If Not Intersect(Target, Range("L2:M500")) Is Nothing Then
>> >> > Target.Interior.ColorIndex = 5
>> >> > End If
>> >> > End Sub
>> >> >
>> >> > Any help is greatly appreciated.
>> >>
>> >> .
>> >>
>>
>> .
>>