Format active cell

[staticbob]

Hi,

How would I format the active cell when it is selected, and the one to its right the same. Then copy the data in the 2nd cell to another.

eg, click on cell A1, both A1 AND A2 are highlighted red, AND the value in A2 is copied to A3

Cheers !
Bob

 

[fairwinds]

Hi,

Maybe:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range(ActiveCell, ActiveCell.Offset(0, 1)).Interior.ColorIndex = 3
ActiveCell.Offset(0, 2) = ActiveCell.Offset(0, 1)
End Sub

 

[SIXTH SENSE]

Hi!
Unfortunately there is no onclick event in xcel.
but you can use the slection change event instead.but i suggest the double click event to do this!

try this one.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    Target.Interior.ColorIndex = 3
    Target.Offset(0, 1).Interior.ColorIndex = 3
    Target.Offset(0, 2).Value = Target.Offset(0, 1).Value
    Cancel = True
End Sub

 

[staticbob]

Cheers FW,

But how do I undo that ? I only want this to show when the cell is active.

Cheers

 

[SIXTH SENSE]

ADVERTISEMENT

ok!
try this

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_BeforeDoubleClick(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range, Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)
    <SPAN style="color:#00007F">Static</SPAN> LastCell <SPAN style="color:#00007F">As</SPAN> CellX
    <SPAN style="color:#00007F">If</SPAN> LastCell.Target <> "" <SPAN style="color:#00007F">Then</SPAN>
        Range(LastCell.Target).Interior.ColorIndex = LastCell.ColorIndex
        Range(LastCell.Target).Offset(0, 1).Interior.ColorIndex = LastCell.ColorIndex
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    LastCell.Target = Target.Address
    LastCell.ColorIndex = Target.Interior.ColorIndex
    Target.Interior.ColorIndex = 3
    Target.Offset(0, 1).Interior.ColorIndex = 3
    Target.Offset(0, 2).Value = Target.Offset(0, 1).Value
    Cancel = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Then paste this in standard module


<font face=Courier New><SPAN style="color:#00007F">Type</SPAN> CellX
    Target <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    ColorIndex <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Type</SPAN>
</FONT>

 

[staticbob]

Thanks SS, that works in practice, but now I need to get it into my file.

The main problem being that I have different sections to apply this to. See below. If I double click on a cell in section 1.1, column B, I want to copy the options from Col C to that sections score.

I have 25 sections ! Do I need to write all this into an IF statement ? If active cell is this, then write to this etc ?

cheers
Bob

Master Op Ass Feb 04.xls
	A	B	C	D
13			Options	Score
14	1.0	PROCUREMENT
15	1.1	TenderType		10
16		BillofQuantities	20
17		CostPlus	20
18		TermMaintenance	15
19		Design&Build	15
20		BillofQuantswithCDP	10
21		Spec&Drawings	10
22		ManagementFee	10
23		Bespokecontract/GMP	5
24		Design&BuildNovated	5
25	1.2	ProcurementRoute		15
26		0ther	?
27		2stage	20
28		Partnered	20
29		Negotiated(tenderlist1of1)	30
30		Competitive	15
31		PPP	15
32		PFI	15
33	1.3	CommercialScope		6
34		Markedsubjectively;between0and10	0-10
35		{anyscoreover5mustsetoutbasisforscore}
Opportunity

 

[SIXTH SENSE]

ADVERTISEMENT

Hi
Is D merge cells?

 

[staticbob]

Yes, unfortunately. Each is merged and applies to the section. The entry in column D would equate to whichever cell had been double-clicked.

I can redesign if this would be better ?

Bob

 

[SIXTH SENSE]

ok!
here I am again!

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_BeforeDoubleClick(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range, Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)
    <SPAN style="color:#00007F">Static</SPAN> LastCell <SPAN style="color:#00007F">As</SPAN> CellX
    <SPAN style="color:#00007F">Set</SPAN> isect = Application.Intersect(Target, Range("b:B"))
    <SPAN style="color:#00007F">If</SPAN> isect <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    <SPAN style="color:#00007F">If</SPAN> LastCell.Target <> "" <SPAN style="color:#00007F">Then</SPAN>
        Range(LastCell.Target).Interior.ColorIndex = LastCell.ColorIndex
        Range(LastCell.Target).Offset(0, 1).Interior.ColorIndex = LastCell.ColorIndex
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    LastCell.Target = Target.Address
    LastCell.ColorIndex = Target.Interior.ColorIndex
    Target.Interior.ColorIndex = 3
    Target.Offset(0, 1).Interior.ColorIndex = 3
    
    <SPAN style="color:#00007F">Set</SPAN> MA = Target.Offset(0, 2).MergeArea
        
        <SPAN style="color:#00007F">If</SPAN> MA.Address = Target.Offset(0, 2).Address <SPAN style="color:#00007F">Then</SPAN>
            Target.Offset(0, 2).Value = Target.Offset(0, 1).Value
        <SPAN style="color:#00007F">Else</SPAN>
            MA.Cells(1, 1).Value = Target.Offset(0, 1).Value
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>

    Cancel = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

 

[staticbob]

Thanks again SS,

But I'm getting compile error . . . user defined type not defined.
??