Formatting cells as a prompt

[SimonHughes]

Hello, I am using Excel 2010

I have a set of seven attributes that need to be selected to chose products from an independent table. I would like to format each step sequentially once the preceding step has been selected.

So - when the cursor is is put in the cell that contains "E SONIQ", the cell immediatly below it (which contains "20") is highlighted in say, yellow. When the cell which contains "20" is selected, it returns to no fill, but the cell below it is highlighted and so on until when the last option (10) is selected, the first cell that contains "E SONIQ" is highlighted.

At any one time, only one cell is highlighted.

Is this possible?
<TABLE style="WIDTH: 177pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=236><COLGROUP><COL style="WIDTH: 30pt; mso-width-source: userset; mso-width-alt: 1462" width=40><COL style="WIDTH: 99pt; mso-width-source: userset; mso-width-alt: 4827" width=132><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 25.5pt" height=34><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #4f81bd; WIDTH: 30pt; HEIGHT: 25.5pt; BORDER-TOP: #4f81bd 0.5pt solid; BORDER-RIGHT: #f0f0f0" id=td_post_2823492 class=xl69 height=34 width=40>Step</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #4f81bd; WIDTH: 99pt; BORDER-TOP: #4f81bd 0.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl69 width=132>Atribute</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #4f81bd; WIDTH: 48pt; BORDER-TOP: #4f81bd 0.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl69 width=64>Input</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 30pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=17 width=40 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 99pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=132>Filter Type</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64>E SONIQ</TD></TR>

<TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 30pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2823492 class=xl68 height=17 width=40 align=right>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 99pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=132>Header size</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64 align=right>20</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 30pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=17 width=40 align=right>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 99pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=132>Filter grade</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64>F7</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 30pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=17 width=40 align=right>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 99pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=132>Filter Height</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64 align=right>592</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 30pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=17 width=40 align=right>5</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 99pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=132>Filter width</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64 align=right>592</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 30pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=17 width=40 align=right>6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 99pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=132>Filter depth</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64 align=right>635</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 30pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=17 width=40 align=right>7</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 99pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=132>Number of pockets</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=64 align=right>10</TD></TR></TBODY></TABLE>

 

[RoryA]

You could do something like this with conditional formatting (assuming C1 to C7 are the cells of interest):
Select C1, then Conditional Formatting on the Home tab, New Rule, 'Use a formula to...', enter:
=C1=""
as the formula and specify your colour.
Now select C2 to C7 and repeat the steps but use this formula:
=AND(C2="",C1<>"")

 

[SimonHughes]

Hi Rorya, it's not working and I suspect it is because I did not make it clear that there will always be an entry is each cell, never a blank.

 

[GlennUK]

Use this method as a starting point ...

http://www.mrexcel.com/td0020.html

except you will be highlighting a different cell to the Target cell, and maybe you want to test whether the Target cell is within your data range. How are your VBA skills?

 

[SimonHughes]

Hi Glenn, VBA I can spell, anything else is a task. I am not sure at all how to incorporate this

 

[GlennUK]

Have a look at the location of this range:

Excel Workbook
	E	F	G
4	Step	Atribute	Input
5	1	Filter Type	E SONIQ
6	2	Header size	20
7	3	Filter grade	F7
8	4	Filter Height	592
9	5	Filter width	592
10	6	Filter depth	635
11	7	Number of pockets	10
Sheet

... and see how it relates to the sheet code ( right click the sheet tab, View Code, and paste this code in there ):

Public OldRng As Range

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not OldRng Is Nothing Then
        OldRng.Interior.ColorIndex = xlNone
    End If
    If Target.Cells.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range("G5:G11")) Is Nothing Then
        rngoffset = 1
        If Target.Row = 11 Then rngoffset = -6
        Target.Interior.ColorIndex = xlNone
        Set OldRng = Target.Offset(rngoffset)
        OldRng.Interior.ColorIndex = 6
    End If
End Sub

 

[SimonHughes]

Nearly! When the active cell is G10 (the last cell to show formatting), the format goes to G11 instead of G4.

I tried to edit the macro but could't correct it.

 

[GlennUK]

According to your original post the last cell to show formatting would be G11, and not G10.

But, if you want it that way, fine. Change all 11's in the code to 10's. And change the -6 to be -5.

 

[SimonHughes]

Got it - I needed to change the values as you suggested but left the -6 alone as this made the correct loop. Many thanks GlennUK, appreciated

 

[GlennUK]

That's great!