excel - Change Value in Adjacent Cell Via Click (VBA right?) -

this first time have ever worked vba stuff (the code found vba right? :p ) please forgive inability just...understand looking @ it. :) have experience macros , know /some/ of code compatible, don't know how write it...only know how record use bits know work.

the puzzle: have column adjust weighted values of row in (either makes values more or less, or leaves them alone), weights entirely subjective need changed manually usually.

the issue when needing change lot of them @ once because tons of clicking each cell , changing value 1 @ time "guessing" weights need entering numbers until "looks good" (like said subjective), found this:

http://www.mrexcel.com/forum/excel-questions/51933-changing-cell-value-clicking-cell.html

made tiny changes version @ bottom of page , have right now:

private sub worksheet_selectionchange(byval target excel.range)   application.enableevents = false   if target.cells.count = 1     if not intersect(target, range("q3:q500")) nothing       select case target.value       case ""         target.value = "+"       case "+"         target.value = "-"       case "-"         target.value = ""       case else         target.value = "+"       end select         activecell.offset(0, 2).range("a1").select     end if   end if   application.enableevents = true end sub 

almost perfect, except range of numbers larger "", +, or -. in fact range 0.5 1.5 (50% 150%).

it best if go or down. guess create huge list of replacements instead of "", +, or - numbers (case 0.5 > case 0.6 > case 0.7 >>> case 1 > case 1.1 >>> etc.) huge nightmare , /less/ efficient when having click cell 10 times cycle through numbers.

so there 2 things need make work perfectly:

1. for change next cell over, either left or right one.
2. instead of simple replacements, possible math?

the idea clicking in column p left of q decrease weight (by 0.1), , clicking in column r right of q increase weight slightly. , if reach bottom/top go other end (0.5 decreased take value 1.5, vice versa).

i tried tinkering little know macros , tried adding "activecell.offset(0, 1).range("a1").select" bit here , there, nice end of code can choose cursor left afterwards...but putting anywhere earlier...i see active cell move p or r before changes made, still changes original column q.

i recognize need 2 copies of code, 1 column r (decreasing) , 1 column p (increasing) long have @ least 1 side should able make changes mirror it. know data validation might seem simple solution, having obscene amount of drop down arrows covering of sheet make illegible...

thank spending time on helping solve puzzle, corinne :)

i think understand question, if doesn't it, please add picture , we'll there.

changing value based on value straightforward.

code shows how handle clicks (or arrow movements) columns p or r. going p decrease value (with wraparound mention). going r go up.

private sub worksheet_selectionchange(byval target excel.range)   application.enableevents = false   if target.cells.count = 1     if not intersect(target, range("p3:p500")) nothing          if target.offset(0, 1).value > 0.5             target.offset(0, 1).value = target.offset(0, 1).value - 0.1         else             target.offset(0, 1).value = 1.5         end if          target.offset(0, 1).select     end if      if not intersect(target, range("r3:r500")) nothing          if target.offset(0, -1).value < 1.5             target.offset(0, -1).value = target.offset(0, -1).value + 0.1         else             target.offset(0, -1).value = 0.5         end if          target.offset(0, -1).select     end if     end if   application.enableevents = true end sub 

the main idea check value in column q , add or subtract or wrap around depending on column , value. code can simplified , cleaned up, works may need.

i assuming have number in column q , trying increase or decrease that? if not, please add more detail.

the nice part of code puts selection column q. means can repeatedly hit right or left arrow increase or decrease, respectively. works because selection changes put in center. can type fast , increment or decrement needed.

picture shows assumption of data in column q. used code couple times change values shown.