Change Cell based on which row the selected Cell is

[Salu]

How can I change the value and background of A1 depending on which row is currently seclected?

I want to be able to see in Cell A1 a percentage value which is found in column EU and different in each row and then conditionally format it depending on what range the % is in.

Under 96% - Red
96% to 100% - Amber
Over 104% -Green

If I select a cell anywhere in a row then the value in A1 would lookup the value in column EU depending on what row I have clicked in.

Does that make sense?

 

[Phanmore]

add this code to the workbook module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("A1").Value = Range("EU" & ActiveCell.Row).Value
End Sub

Then just add conditional formating to cell A1

 

[Peter_SSs]

How can I change the value and background of A1 depending on which row is currently seclected?

I want to be able to see in Cell A1 a percentage value which is found in column EU and different in each row and then conditionally format it depending on what range the % is in.

Under 96% - Red
96% to 100% - Amber
Over 104% -Green

If I select a cell anywhere in a row then the value in A1 would lookup the value in column EU depending on what row I have clicked in.

Does that make sense?

You would apply normal Conditional Formatting to cell A1 to apply the colours. However, did you mean to leave a gap? That is, what colour should A1 be if the percentage is between 100% and 104%?

To get the column EU value into cell A1 ..

1. Right click the sheet name tab and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

3. Close the Visual Basic window.

4. Try selecting different cells.

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_SelectionChange(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    Range("A1").Value = Range("EU" & ActiveCell.Row).Value<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[Peter_SSs]

add this code to the workbook module

Sorry, I hadn't seen your response before I posted mine, but surely this should say the Worksheet module not the Workbook module?

 

[Phanmore]

surely this should say the Worksheet module not the Workbook module?

Appologies, Worksheet :D

 

[Salu]

Thanks guys.

I can't see anything working though. Do I have to enable something such as macro security in order to see the changes?

I didn't mean to leave out a range, thanks for that.

I also forgot to mention that Row 1 and 2 are taken up with headers. Would that stop the routine working?

I can't connect what I should be putting as conditional formatting in A1 either. I'm used to using conditional formatting but not sure what would be changing in A1 to work out the conditions...?

 

[Peter_SSs]

1. You will need to have macros enabled. What version of Excel are you using?

2. Replcae the existing code supplied with this code (teporarily at least)

<font face=Courier New><br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_SelectionChange(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    MsgBox "Code is working"<br>    Range("A3").Value = Range("EU" & ActiveCell.Row).Value<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>


3. It is possible that your code is not working because 'Events' (eg Worksheet_Change) are disabled. In the VBA window, ensure the Immediate Window is visible (View|Immediate Window) and then on a new line in the Immediate Window, type
Application.EnableEvents=True and press Enter

Now when you select a cell, do you get the message box pop up?

 

[Salu]

Great. Now a popup says "code working" after doing what you said and enabling macros.

I'm using 2007 but want it to work in 2003 too..

Thanks

 

[Peter_SSs]

Great. Now a popup says "code working" after doing what you said and enabling macros.

I'm using 2007 but want it to work in 2003 too..

Thanks

OK, so now take out the MsgBox line. The code should work just as well in Excel 2003.

Now you have to select cell A3 (if that's the cell in the code) and apply Conditional Formatting something like this. You may have to tweak it a little since you didn't say which colour to make the 'missing' range.

Excel Workbook
	A
3	97%
EU Value

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A3	1. / Formula is =AND(A3<>"",A3<0.96)	Abc
A3	2. / Formula is =AND(A3<>"",A3<=1.04)	Abc
A3	3. / Formula is =A3>1.04	Abc

 

[Salu]

It works!!

Thanks Peter. I'm very grateful.