Worksheet_Change

M

monkeyharris

Active Member
Joined
Jan 20, 2008
Messages
358
Office Version
  1. 365
Platform
  1. Windows
Please help, I have entered the below code which produces the second table but don't now how to do the same for more than one table in the same sheet.

I hope you can help.
:p
<TABLE style="WIDTH: 610pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=812 border=0 x:str><COLGROUP><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" span=3 width=96><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4315" span=2 width=118><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" span=3 width=96><TBODY><TR style="HEIGHT: 14.25pt" height=19><TD class=xl32 style="BORDER-RIGHT: black 2pt double; BORDER-TOP: windowtext 2pt double; BORDER-LEFT: windowtext 2pt double; WIDTH: 394pt; BORDER-BOTTOM: windowtext 2pt double; HEIGHT: 14.25pt; BACKGROUND-COLOR: silver" width=524 colSpan=5 height=19>CUBIC METRES CALCULATOR USING INCHES</TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 72pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver" width=96></TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 72pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver" width=96></TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 72pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver" width=96></TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl26 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 2pt double; BORDER-BOTTOM: windowtext 2pt double; HEIGHT: 14.25pt; BACKGROUND-COLOR: silver" height=19>Width (In)</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: silver">Length (In)</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: silver">Height (In)</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: silver">No. of Packages</TD><TD class=xl26 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: silver">Cubic Metres</TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl27 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 2pt double; BORDER-BOTTOM: windowtext 2pt double; HEIGHT: 14.25pt; BACKGROUND-COLOR: #ffff99" height=19 x:num>23.62</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: #ffff99" x:num>23.62</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: #ffff99" x:num>23.62</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: #ffff99" x:num>1</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: silver" x:num="0.2159438448670597" x:fmla="=E4">0.22</TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: silver" height=18></TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl24 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver" align=middle x:num="13177.701928" x:fmla="=A3*B3*C3">##################</TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver" align=right x:num="0.2159438448670597" x:fmla="=D4/61023.7440947322">0.21594384</TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: silver" height=17></TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: silver" height=18></TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl32 style="BORDER-RIGHT: black 2pt double; BORDER-TOP: windowtext 2pt double; BORDER-LEFT: windowtext 2pt double; BORDER-BOTTOM: windowtext 2pt double; HEIGHT: 14.25pt; BACKGROUND-COLOR: silver" colSpan=8 height=19>CONVERTING LENGTH</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl30 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 2pt double; WIDTH: 72pt; BORDER-BOTTOM: windowtext 2pt double; HEIGHT: 14.25pt; BACKGROUND-COLOR: silver" width=96 height=19>Millimeters</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 72pt; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: silver" width=96>Centimeters</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 72pt; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: silver" width=96>Inches</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 89pt; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: silver" width=118>Feet</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 89pt; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: silver" width=118>Yards</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 72pt; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: silver" width=96>Meter</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 72pt; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: silver" width=96>Kilometers</TD><TD class=xl30 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 72pt; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: silver" width=96>Miles</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl31 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 2pt double; BORDER-BOTTOM: windowtext 2pt double; HEIGHT: 14.25pt; BACKGROUND-COLOR: #ffff99" height=19 x:num>304.80000</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: #ffff99" x:num>30.48000</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: #ffff99" x:num>12.00000</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: #ffff99" x:num>1.00000</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: #ffff99" x:num="0.33333000000000002">0.33333</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: #ffff99" x:num="0.30480000000000002">0.30480</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: #ffff99" x:num="2.9999999999999997E-4">0.00030</TD><TD class=xl31 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: #ffff99" x:num="1.9000000000000001E-4">0.00019</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 13.5pt; BACKGROUND-COLOR: silver" height=18></TD><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl28 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl23 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD></TR></TBODY></TABLE>

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("D26:K26")) Is Nothing Then
Application.EnableEvents = False

Select Case Target.Address

Case "$D$26"

Range("E26") = Range("D26") / 10
Range("F26") = Range("D26") / 25.4
Range("G26") = Range("D26") / 304.8
Range("H26") = Range("D26") / 914.4
Range("I26") = Range("D26") / 1000
Range("G26") = Range("D26") / 1000000
Range("K26") = Range("D26") / 1609344

Case "$E$26"

Range("D26") = Range("E26") * 10
Range("F26") = Range("E26") * 0.3937
Range("G26") = Range("E26") * 0.03281
Range("H26") = Range("E26") * 0.01094
Range("I26") = Range("E26") * 0.01
Range("J26") = Range("E26") * 0.00001
Range("K26") = Range("E26") * 0.00001

Case "$F$26"

Range("D26") = Range("F26") * 25.4
Range("E26") = Range("F26") * 2.54
Range("G26") = Range("F26") * 0.08333
Range("H26") = Range("F26") * 0.02778
Range("I26") = Range("F26") * 0.0254
Range("J26") = Range("F26") * 0.00003
Range("K26") = Range("F26") * 0.00002

Case "$G$26"

Range("D26") = Range("G26") * 304.8
Range("E26") = Range("G26") * 30.48
Range("F26") = Range("G26") * 12
Range("H26") = Range("G26") * 0.33333
Range("I26") = Range("G26") * 0.3048
Range("J26") = Range("G26") * 0.0003
Range("K26") = Range("G26") * 0.00019
Case "$H$26"

Range("D26") = Range("H26") * 914.4
Range("E26") = Range("H26") * 91.44
Range("F26") = Range("H26") * 36
Range("G26") = Range("H26") * 3
Range("I26") = Range("H26") * 0.9144
Range("G26") = Range("H26") * 0.00091
Range("K26") = Range("H26") * 0.00057
Case "$I$26"

Range("D26") = Range("I26") * 1000
Range("E26") = Range("I26") * 100
Range("F26") = Range("I26") * 39.3701
Range("G26") = Range("I26") * 3.28084
Range("H26") = Range("I26") * 1.09361
Range("J26") = Range("I26") * 0.001
Range("K26") = Range("I26") * 0.00062
Case "$J$26"

Range("D26") = Range("J26") * 1000000
Range("E26") = Range("J26") * 100000
Range("F26") = Range("J26") * 39370.1
Range("G26") = Range("J26") * 3280.84
Range("H26") = Range("J26") * 1093.61
Range("I26") = Range("J26") * 1000
Range("K26") = Range("J26") * 0.62137
Case "$K$26"

Range("D26") = Range("K26") * 1609344
Range("E26") = Range("K26") * 160934
Range("F26") = Range("K26") * 63360
Range("G26") = Range("K26") * 5280
Range("H26") = Range("K26") * 1760
Range("I26") = Range("K26") * 1609
Range("J26") = Range("K26") * 1.60934

End Select

Application.EnableEvents = True
End If
End Sub
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Sort by date Sort by votes
You can do this by using nested Select Case statements, but I'm not clear what you want from your example. Where are the cells that when changed will trigger the event and where are the destination cells?

lenze
 
Upvote 0
Lenze,

My first thread probably doesn't give the best example. Below the first table relates to the code i've done. I tried adding a similar code for the second tble but it wouldn't work. THe first one works OK.

Mark

<TABLE style="WIDTH: 610pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=812 border=0 x:str><COLGROUP><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" span=3 width=96><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4315" span=2 width=118><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" span=3 width=96><TBODY><TR style="HEIGHT: 14.25pt" height=19><TD class=xl29 style="BORDER-RIGHT: black 2pt double; BORDER-TOP: windowtext 2pt double; BORDER-LEFT: windowtext 2pt double; WIDTH: 610pt; BORDER-BOTTOM: windowtext 2pt double; HEIGHT: 14.25pt; BACKGROUND-COLOR: silver" width=812 colSpan=8 height=19>CONVERTING LENGTH</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl27 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 2pt double; WIDTH: 72pt; BORDER-BOTTOM: windowtext 2pt double; HEIGHT: 14.25pt; BACKGROUND-COLOR: silver" width=96 height=19>Millimeters</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 72pt; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: silver" width=96>Centimeters</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 72pt; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: silver" width=96>Inches</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 89pt; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: silver" width=118>Feet</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 89pt; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: silver" width=118>Yards</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 72pt; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: silver" width=96>Meter</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 72pt; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: silver" width=96>Kilometers</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 72pt; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: silver" width=96>Miles</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl28 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 2pt double; BORDER-BOTTOM: windowtext 2pt double; HEIGHT: 14.25pt; BACKGROUND-COLOR: #ffff99" height=19 x:num>304.80000</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: #ffff99" x:num>30.48000</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: #ffff99" x:num>12.00000</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: #ffff99" x:num>1.00000</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: #ffff99" x:num="0.33333000000000002">0.33333</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: #ffff99" x:num="0.30480000000000002">0.30480</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: #ffff99" x:num="2.9999999999999997E-4">0.00030</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: #ffff99" x:num="1.9000000000000001E-4">0.00019</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 14.25pt; BACKGROUND-COLOR: silver" height=19></TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl26 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD><TD class=xl25 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: silver"></TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl29 style="BORDER-RIGHT: black 2pt double; BORDER-TOP: windowtext 2pt double; BORDER-LEFT: windowtext 2pt double; BORDER-BOTTOM: windowtext 2pt double; HEIGHT: 14.25pt; BACKGROUND-COLOR: silver" colSpan=8 height=19>CONVERTING WEIGHT</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl27 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 2pt double; WIDTH: 72pt; BORDER-BOTTOM: windowtext 2pt double; HEIGHT: 14.25pt; BACKGROUND-COLOR: silver" width=96 height=19>A</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 72pt; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: silver" width=96>B</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 72pt; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: silver" width=96>C</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 89pt; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: silver" width=118>D</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 89pt; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: silver" width=118>E</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 72pt; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: silver" width=96>F</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 72pt; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: silver" width=96>G</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 72pt; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: silver" width=96>H</TD></TR><TR style="HEIGHT: 14.25pt" height=19><TD class=xl28 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 2pt double; BORDER-BOTTOM: windowtext 2pt double; HEIGHT: 14.25pt; BACKGROUND-COLOR: #ffff99" height=19 x:num>1.00000</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: #ffff99" x:num>2.00000</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: #ffff99" x:num>3.00000</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: #ffff99" x:num>4.00000</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: #ffff99" x:num>5.00000</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: #ffff99" x:num>6.00000</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: #ffff99" x:num>7.00000</TD><TD class=xl28 style="BORDER-RIGHT: windowtext 2pt double; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 2pt double; BACKGROUND-COLOR: #ffff99" x:num>8.00000</TD></TR></TBODY></TABLE>
 
Upvote 0
HI:
Still not clear. Are you clicking on yellow cells? Can they be in any column(D:K)? What is being populated by your macro?

lenze
 
Upvote 0
As an example, in the first table i would enter 1 (one) in the yellow field under MILES, this would then automatically update all other cells in yellow in table 1 (one) to show how many inches, yards, metres etc are equal to 1 mile.

The second table is totally seperate to table one, but the same principle applies. If you look at the following web page it give you a better idea of what i'm trying to do. http://www.worldwidemetric.com/metcal.htm
 
Upvote 0
So if you entered 1 under yards, it would also convert all the others. I trust you have the code do do that. So a simple approach. Select all your yellow cells in the 2 tables. Use the CTRL key because you have non-contiguous cells. With these selected , give them a name (say myCells). Now you code would be
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("myCells")) Is Nothing Then Exit Sub
Select Case Target.Address
    Case "$D$26"
    'your code
    Case "$E$26"
    'your code
    'etc
End Select
End Sub

lenze
 
Upvote 0
Some small additions to the above code
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Not Application.WorkSheetFunction.IsNumber(Target) Then Exit Sub
If Intersect(Target, Range("myCells")) Is Nothing Then Exit Sub
Application.EnableEvents = False
Select Case Target.Address
    Case "$D$26"
    'your code
    Case "$E$26"
    'your code
    'etc
End Select
Application.EnableEvents = True
End Sub

Also the lines in your code can be rewritten, so
Code: 
Range("E26") = Range("D26") / 10
could be
Code: 
Range("E26") = Target/10
or even
Code: 
Cells(Target.Row,5) = Target/10

HTH
lenze
 
Upvote 0
Actually, after thinking about it, I would write the code like this.
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("myCells")) Is Nothing Then Exit Sub
If Not Application.WorkSheetFunction.IsNumber(Target) Then 
    MsgBox "Invalid Entry. Try Again"
    Application.EnableEvents = False
    Application.Undo
    Application.EnableEvents = True
    Exit Sub
End If
Application.EnableEvents = False
Select Case Target.Address
    Case "$D$26"
    'your code
    Case "$E$26"
    'your code
    'etc
End Select
Application.EnableEvents = True
End Sub

lenze
 
Upvote 0
Lenze,

I've just tried the below and got an error in the line with red text. I've only used the one table so far.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("D26, F26")) Is Nothing Then Exit Sub
If Not Application.WorksheetFunction.IsNumber(Target) Then
MsgBox "Invalid Entry. Try Again"
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
Exit Sub
End If
Application.EnableEvents = False
Select Case Target.Address
Case "$D$26"

Range("E26") = Range("D26") / 10
Range("F26") = Range("D26") / 25.4
Range("G26") = Range("D26") / 304.8
Range("H26") = Range("D26") / 914.4
Range("I26") = Range("D26") / 1000
Range("G26") = Range("D26") / 1000000
Range("K26") = Range("D26") / 1609344

Case "$E$26"
Range("D26") = Range("E26") * 10
Range("F26") = Range("E26") * 0.3937
Range("G26") = Range("E26") * 0.03281
Range("H26") = Range("E26") * 0.01094
Range("I26") = Range("E26") * 0.01
Range("J26") = Range("E26") * 0.00001
Range("K26") = Range("E26") * 0.00062 / 100

Case "$F$26"

Range("D26") = Range("F26") * 25.4
Range("E26") = Range("F26") * 2.54
Range("G26") = Range("F26") * 0.08333
Range("H26") = Range("F26") * 0.02778
Range("I26") = Range("F26") * 0.0254
Range("J26") = Range("F26") * 0.00003
Range("K26") = Range("F26") * 0.00002

Case "$G$26"

Range("D26") = Range("G26") * 304.8
Range("E26") = Range("G26") * 30.48
Range("F26") = Range("G26") * 12
Range("H26") = Range("G26") * 0.33333
Range("I26") = Range("G26") * 0.3048
Range("J26") = Range("G26") * 0.0003
Range("K26") = Range("G26") * 0.00019
Case "$H$26"

Range("D26") = Range("H26") * 914.4
Range("E26") = Range("H26") * 91.44
Range("F26") = Range("H26") * 36
Range("G26") = Range("H26") * 3
Range("I26") = Range("H26") * 0.9144
Range("G26") = Range("H26") * 0.00091
Range("K26") = Range("H26") * 0.00057
Case "$I$26"

Range("D26") = Range("I26") * 1000
Range("E26") = Range("I26") * 100
Range("F26") = Range("I26") * 39.3701
Range("G26") = Range("I26") * 3.28084
Range("H26") = Range("I26") * 1.09361
Range("J26") = Range("I26") * 0.001
Range("K26") = Range("I26") * 0.00062
Case "$J$26"

Range("D26") = Range("J26") * 1000000
Range("E26") = Range("J26") * 100000
Range("F26") = Range("J26") * 39370.1
Range("G26") = Range("J26") * 3280.84
Range("H26") = Range("J26") * 1093.61
Range("I26") = Range("J26") * 1000
Range("K26") = Range("J26") * 0.62137
Case "$K$26"

Range("D26") = Range("K26") * 1609344
Range("E26") = Range("K26") * 160934
Range("F26") = Range("K26") * 63360
Range("G26") = Range("K26") * 5280
Range("H26") = Range("K26") * 1760
Range("I26") = Range("K26") * 1609
Range("J26") = Range("K26") * 1.60934

End Select
Application.EnableEvents = True
End Sub
 
Upvote 0
Just caught your post before leaving.
You have to use the method I discribed above. You can not use
Code: 
If Intersect(Target, Range("D26, F26")) Is Nothing Then Exit Sub
Select all your yellow cells in the 2 tables. Use the CTRL key because you have non-contiguous cells. With these selected, give them a name (say myCells).
Click to expand...

Now use
Code: 
If Intersect(Target, Range("myCells")) Is Nothing Then Exit Sub

That simple
lenze
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
1K
Attrazion
A
R
  • Question
Macro not working after adding two new items
Replies
4
Views
146
myall_blues
myall_blues
G
  • Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
856
gvsumich
G
E
  • Question
VBA for Web Scraping help
Replies
0
Views
609
epactheactor
E
E
  • Question
Adding a custom message.
Replies
1
Views
451
Micron
M

Forum statistics

Threads
1,215,676
Messages
6,126,168
Members
449,296
Latest member
tinneytwin

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top