How can I high light a group of cells from different active cells? For
example, when cell A1 is the active cell, I would like to high light cells
D1:F3, A2 highlight cells D5:F7 etc.

By "highlight" do you mean "select" or "change color"?

If change color, do you want D1:F3 uncolored when D5:F7 are colored?

What is the extent of "etc."?


Gord Dibben MS Excel MVP

On Mon, 16 Apr 2007 09:26:05 -0700, village_idiot
wrote:

How can I high light a group of cells from different active cells? For
example, when cell A1 is the active cell, I would like to high light cells
D1:F3, A2 highlight cells D5:F7 etc.

Use an event macro:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.Interior.ColorIndex = xlNone
If Intersect(Target, Range("A1")) Is Nothing Then
Else
Range("D1:F3").Interior.ColorIndex = 6
End If

If Intersect(Target, Range("A2")) Is Nothing Then
Else
Range("D5:F7").Interior.ColorIndex = 6
End If

End Sub


This goes in Worksheet code, not a standard module.
--
Gary''s Student - gsnu200715

That's for the info, I'll try it after work. In answer to Gord Dibben, I
have to do this 81 times. I made a spread sheet to help play Sudoku.

"Gary''s Student" wrote:

Use an event macro:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.Interior.ColorIndex = xlNone
If Intersect(Target, Range("A1")) Is Nothing Then
Else
Range("D1:F3").Interior.ColorIndex = 6
End If

If Intersect(Target, Range("A2")) Is Nothing Then
Else
Range("D5:F7").Interior.ColorIndex = 6
End If

End Sub


This goes in Worksheet code, not a standard module.
--
Gary''s Student - gsnu200715

I sould say Thanks not That's

"village_idiot" wrote:

That's for the info, I'll try it after work. In answer to Gord Dibben, I
have to do this 81 times. I made a spread sheet to help play Sudoku.

"Gary''s Student" wrote:

Use an event macro:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.Interior.ColorIndex = xlNone
If Intersect(Target, Range("A1")) Is Nothing Then
Else
Range("D1:F3").Interior.ColorIndex = 6
End If

If Intersect(Target, Range("A2")) Is Nothing Then
Else
Range("D5:F7").Interior.ColorIndex = 6
End If

End Sub


This goes in Worksheet code, not a standard module.
--
Gary''s Student - gsnu200715

One more question, I take it that 6 is the color, is there a list of the
different colors and their numbers?

"Gary''s Student" wrote:

Use an event macro:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.Interior.ColorIndex = xlNone
If Intersect(Target, Range("A1")) Is Nothing Then
Else
Range("D1:F3").Interior.ColorIndex = 6
End If

If Intersect(Target, Range("A2")) Is Nothing Then
Else
Range("D5:F7").Interior.ColorIndex = 6
End If

End Sub


This goes in Worksheet code, not a standard module.
--
Gary''s Student - gsnu200715

Yes 6 is a nice bright yellow.


In:

http://www.mvps.org/dmcritchie/excel/colors.htm

see the section:
ColorIndex -- 56 Excel Colors [#colors56/#colorindex]


for a complete list

--
Gary''s Student - gsnu200715


"village_idiot" wrote:

One more question, I take it that 6 is the color, is there a list of the
different colors and their numbers?

"Gary''s Student" wrote:

Use an event macro:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.Interior.ColorIndex = xlNone
If Intersect(Target, Range("A1")) Is Nothing Then
Else
Range("D1:F3").Interior.ColorIndex = 6
End If

If Intersect(Target, Range("A2")) Is Nothing Then
Else
Range("D5:F7").Interior.ColorIndex = 6
End If

End Sub


This goes in Worksheet code, not a standard module.
--
Gary''s Student - gsnu200715

Again thanks for the help, but I'm having problems and the debug program says
that the problem is with the line that reads "Cells.Interior.ColorIndex =
x|none". The worksheet has some cells of different colors already, how do I
change that line to keep the other cells color and only change the ranges of
cells I want changed

"Gary''s Student" wrote:

Use an event macro:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.Interior.ColorIndex = xlNone
If Intersect(Target, Range("A1")) Is Nothing Then
Else
Range("D1:F3").Interior.ColorIndex = 6
End If

If Intersect(Target, Range("A2")) Is Nothing Then
Else
Range("D5:F7").Interior.ColorIndex = 6
End If

End Sub


This goes in Worksheet code, not a standard module.
--
Gary''s Student - gsnu200715

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Intersect(Target, Range("A1")) Is Nothing Then
Else
Range("D1:F3").Interior.ColorIndex = 6
Range("D5:F7").Interior.ColorIndex = xlNone
End If

If Intersect(Target, Range("A2")) Is Nothing Then
Else
Range("D5:F7").Interior.ColorIndex = 6
Range("D1:F3").Interior.ColorIndex = xlNone
End If

End Sub

This version does not clear all the formats, just the ones the logic is
controlling
--
Gary''s Student - gsnu200715

Maybe, instead of re-inventing wheel, you could download Andy Pope's sudoku
puzzle generator and solver.

http://www.andypope.info/fun/sudoku.htm




On Mon, 16 Apr 2007 10:12:03 -0700, village_idiot
wrote:

That's for the info, I'll try it after work. In answer to Gord Dibben, I
have to do this 81 times. I made a spread sheet to help play Sudoku.

"Gary''s Student" wrote:

Use an event macro:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells.Interior.ColorIndex = xlNone
If Intersect(Target, Range("A1")) Is Nothing Then
Else
Range("D1:F3").Interior.ColorIndex = 6
End If

If Intersect(Target, Range("A2")) Is Nothing Then
Else
Range("D5:F7").Interior.ColorIndex = 6
End If

End Sub


This goes in Worksheet code, not a standard module.
--
Gary''s Student - gsnu200715

Again, thanks and a question. Can I name the range of cells? Like I said
I'm going to have 81 cells to do this with, with about 629 cells for each of
the 81 to clear of color. I'm sorry to be such a pain, but I'm self taught
and so far I haven't taught myself anything about this kind of stuff, but I'm
learning from you.

"Gary''s Student" wrote:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Intersect(Target, Range("A1")) Is Nothing Then
Else
Range("D1:F3").Interior.ColorIndex = 6
Range("D5:F7").Interior.ColorIndex = xlNone
End If

If Intersect(Target, Range("A2")) Is Nothing Then
Else
Range("D5:F7").Interior.ColorIndex = 6
Range("D1:F3").Interior.ColorIndex = xlNone
End If

End Sub

This version does not clear all the formats, just the ones the logic is
controlling
--
Gary''s Student - gsnu200715

You are not a pain. This is fun. Yes you can use Named Ranges ( which we
can define in either the worksheet or the macro). We can gives names to each
of the small blocks of cells and make a super range by combining them. Or if
all the small blocks are in cols D-F, then we can clear the color out of just
these three columns and paint the color in the selected small block.


There are lots of options.
--
Gary''s Student - gsnu200715

I may not be a pain, but like I said I'm self-taught and my teacher is an
idiot. If it would help you, I can send a copy of my Sudoku worksheet and
you can see what I'm trying to do. I'm a newbie at this site, so I don't
know if it's allowed to put our e-mail address in, but if it's ok I'll get in
touch with you and give you a copy, as long you don't laugh to hard at the
way I did things.

"Gary''s Student" wrote:

You are not a pain. This is fun. Yes you can use Named Ranges ( which we
can define in either the worksheet or the macro). We can gives names to each
of the small blocks of cells and make a super range by combining them. Or if
all the small blocks are in cols D-F, then we can clear the color out of just
these three columns and paint the color in the selected small block.


There are lots of options.
--
Gary''s Student - gsnu200715

adjust lukewarm to hot and send it to :


--
Gary''s Student - gsnu200715


"village_idiot" wrote:

I may not be a pain, but like I said I'm self-taught and my teacher is an
idiot. If it would help you, I can send a copy of my Sudoku worksheet and
you can see what I'm trying to do. I'm a newbie at this site, so I don't
know if it's allowed to put our e-mail address in, but if it's ok I'll get in
touch with you and give you a copy, as long you don't laugh to hard at the
way I did things.

"Gary''s Student" wrote:

You are not a pain. This is fun. Yes you can use Named Ranges ( which we
can define in either the worksheet or the macro). We can gives names to each
of the small blocks of cells and make a super range by combining them. Or if
all the small blocks are in cols D-F, then we can clear the color out of just
these three columns and paint the color in the selected small block.


There are lots of options.
--
Gary''s Student - gsnu200715

I've been working with your info and came to the conclusion that it will be a
very big answer to my question (81 bunchs of 11 or 12 statements each). So,
what would the formula be to use conditional formating to do the trick? In
other words, how do I tell the conditional format to activate only when a
certain cell is active?

"Gary''s Student" wrote:

adjust lukewarm to hot and send it to :


--
Gary''s Student - gsnu200715


"village_idiot" wrote:

I may not be a pain, but like I said I'm self-taught and my teacher is an
idiot. If it would help you, I can send a copy of my Sudoku worksheet and
you can see what I'm trying to do. I'm a newbie at this site, so I don't
know if it's allowed to put our e-mail address in, but if it's ok I'll get in
touch with you and give you a copy, as long you don't laugh to hard at the
way I did things.

"Gary''s Student" wrote:

You are not a pain. This is fun. Yes you can use Named Ranges ( which we
can define in either the worksheet or the macro). We can gives names to each
of the small blocks of cells and make a super range by combining them. Or if
all the small blocks are in cols D-F, then we can clear the color out of just
these three columns and paint the color in the selected small block.


There are lots of options.
--
Gary''s Student - gsnu200715

Hi v.i

Conditional Formatting is not sensitive to the active cell, but if you put a
value in a cell, Condtional Formatting can respond to it. Use Formula Is


=$E$9<""


or some other form.
--
Gary''s Student - gsnu200716


"village_idiot" wrote:

I've been working with your info and came to the conclusion that it will be a
very big answer to my question (81 bunchs of 11 or 12 statements each). So,
what would the formula be to use conditional formating to do the trick? In
other words, how do I tell the conditional format to activate only when a
certain cell is active?

"Gary''s Student" wrote:

adjust lukewarm to hot and send it to :


--
Gary''s Student - gsnu200715


"village_idiot" wrote:

I may not be a pain, but like I said I'm self-taught and my teacher is an
idiot. If it would help you, I can send a copy of my Sudoku worksheet and
you can see what I'm trying to do. I'm a newbie at this site, so I don't
know if it's allowed to put our e-mail address in, but if it's ok I'll get in
touch with you and give you a copy, as long you don't laugh to hard at the
way I did things.

"Gary''s Student" wrote:

You are not a pain. This is fun. Yes you can use Named Ranges ( which we
can define in either the worksheet or the macro). We can gives names to each
of the small blocks of cells and make a super range by combining them. Or if
all the small blocks are in cols D-F, then we can clear the color out of just
these three columns and paint the color in the selected small block.


There are lots of options.
--
Gary''s Student - gsnu200715

Thanks Gary"s most excellant student

I wanted to use the conditional formatting before entering anything in the
cell. So, I guess it's back to the otherway.

"Gary''s Student" wrote:

Hi v.i

Conditional Formatting is not sensitive to the active cell, but if you put a
value in a cell, Condtional Formatting can respond to it. Use Formula Is


=$E$9<""


or some other form.
--
Gary''s Student - gsnu200716


"village_idiot" wrote:

I've been working with your info and came to the conclusion that it will be a
very big answer to my question (81 bunchs of 11 or 12 statements each). So,
what would the formula be to use conditional formating to do the trick? In
other words, how do I tell the conditional format to activate only when a
certain cell is active?

"Gary''s Student" wrote:

adjust lukewarm to hot and send it to :


--
Gary''s Student - gsnu200715


"village_idiot" wrote:

I may not be a pain, but like I said I'm self-taught and my teacher is an
idiot. If it would help you, I can send a copy of my Sudoku worksheet and
you can see what I'm trying to do. I'm a newbie at this site, so I don't
know if it's allowed to put our e-mail address in, but if it's ok I'll get in
touch with you and give you a copy, as long you don't laugh to hard at the
way I did things.

"Gary''s Student" wrote:

You are not a pain. This is fun. Yes you can use Named Ranges ( which we
can define in either the worksheet or the macro). We can gives names to each
of the small blocks of cells and make a super range by combining them. Or if
all the small blocks are in cols D-F, then we can clear the color out of just
these three columns and paint the color in the selected small block.


There are lots of options.
--
Gary''s Student - gsnu200715

With a lot of help from Gary"s_Student I got the job done! I still think
that there should be a way to use a formula in a Conditional Format, but I
couldn't find a way to get it to see what cell is active.

"village_idiot" wrote:

I've been working with your info and came to the conclusion that it will be a
very big answer to my question (81 bunchs of 11 or 12 statements each). So,
what would the formula be to use conditional formating to do the trick? In
other words, how do I tell the conditional format to activate only when a
certain cell is active?

"Gary''s Student" wrote:

adjust lukewarm to hot and send it to :


--
Gary''s Student - gsnu200715


"village_idiot" wrote:

I may not be a pain, but like I said I'm self-taught and my teacher is an
idiot. If it would help you, I can send a copy of my Sudoku worksheet and
you can see what I'm trying to do. I'm a newbie at this site, so I don't
know if it's allowed to put our e-mail address in, but if it's ok I'll get in
touch with you and give you a copy, as long you don't laugh to hard at the
way I did things.

"Gary''s Student" wrote:

You are not a pain. This is fun. Yes you can use Named Ranges ( which we
can define in either the worksheet or the macro). We can gives names to each
of the small blocks of cells and make a super range by combining them. Or if
all the small blocks are in cols D-F, then we can clear the color out of just
these three columns and paint the color in the selected small block.


There are lots of options.
--
Gary''s Student - gsnu200715