excel vba - Applying code to colored cells only in VBA excell -


i trying adapt code found of our recipe sheets take scanned entries instead of typed. cell require input colored orange. these cells accept scanned entries. far have apply code column "i" , colors orange. not color cells use colored cells. heres have far

..

option explicit  declare function setwindowshookex lib _ "user32" alias "setwindowshookexa" (byval idhook long, byval lpfn long, _ byval hmod long, byval dwthreadid long) long declare function callnexthookex lib "user32" _ (byval hhook long, byval ncode long, byval wparam long, lparam any) long  declare function unhookwindowshookex lib "user32" (byval hhook long) long  declare function getactivewindow lib "user32" () long  declare function findwindow lib "user32" alias "findwindowa" _ (byval lpclassname string, byval lpwindowname string) long  const hc_action = 0 const wm_keydown = &h100 const wh_keyboard_ll = 13 dim hhklowlevelkybd long dim blnhookenabled boolean dim enumallowedvalues allowedvalues dim objtargetrange range dim objvalidationrange range dim vans variant  type kbdllhookstruct     vkcode long     scancode long     flags long     time long     dwextrainfo long end type  enum allowedvalues     alpha     numeric end enum     function lowlevelkeyboardproc _ (byval ncode long, byval wparam long, lparam kbdllhookstruct) long      '\hook keyboard if xl active window     if getactivewindow = findwindow("xlmain", application.caption)         if (ncode = hc_action)             '\check if key pushed             if wparam = wm_keydown             '\if so, check if active cell within target range                 if union(activecell, objtargetrange).address = objtargetrange.address                 '\if numeric values should allowed                     if enumallowedvalues = 1                     '\check if pushed key numeric key or navigation key                     '\by checking vkcode stored in laparm parameter                         if chr(lparam.vkcode) "#" or _                             lparam.vkcode = 37 or lparam.vkcode = 38 or lparam.vkcode = 39 or _                             lparam.vkcode = 40 or lparam.vkcode = 9 or lparam.vkcode = 13                             '\if allow input                             lowlevelkeyboardproc = 0                         else                             '\else filter out key_down message message qeue                             beep                             lowlevelkeyboardproc = -1                             exit function                         end if                         '\if onle alpha values should allowed                     elseif enumallowedvalues = 0                         '\check laparam parameter                         if chr(lparam.vkcode) "#"                             '\if numeric prevent input                             beep                             lowlevelkeyboardproc = -1                             exit function                         else                             '\otherwise allow input                             lowlevelkeyboardproc = 0                     end if                     end if                 end if             end if         end if     end if     '\pass function next hook if there 1     lowlevelkeyboardproc = callnexthookex(0, ncode, wparam, byval lparam)  end function   public sub unhook_keyboard()      if hhklowlevelkybd <> 0 unhookwindowshookex hhklowlevelkybd     blnhookenabled = false   end sub   sub validaterange(r range, byval v allowedvalues)      '\store these in global variables     '\needed later in filter function     enumallowedvalues = v     set objtargetrange = r     '\don't hook keyboard twice !!     if blnhookenabled = false         hhklowlevelkybd = setwindowshookex _         (wh_keyboard_ll, addressof lowlevelkeyboardproc, application.hinstance, 0)         blnhookenabled = true     end if  end sub   sub test()      '\ignore mishandling of following     '\input boxes user     on error resume next     set objvalidationrange = sheets("sheet1").range("i:i")     if objvalidationrange nothing goto errhdlr         objvalidationrange.interior.colorindex = 44         vans = 2         if vans = 2             validaterange objvalidationrange, allowedvalues.numeric + alpha         else         goto errhdlr     end if     objvalidationrange.cells(1).select     set objvalidationrange = nothing     exit sub errhdlr:     msgbox "criteria error- try again !", vbcritical     unhook_keyboard  end sub

if you're sure cells/rows wish modify indeed characterized 

.interior.colorindex = 44

then have make check , continue logic if comes out positive:

if mycell.interior.colorindex = 44 ' stuff end if

in case color other simple colorindex = 44, can check in immediate window using 

?mycell.interior.color

you can copy color value (it's output numeric value) , paste in condition, remember differentiate between color , colorindex.


Comments

Post a Comment

Popular posts from this blog

Is there a better way to structure post methods in Class Based Views -

i'm using class based views in django , never specify url in ajax calls , omit action parameter in form because know these requests go through post method of class based view. notice view - profile page make many requests depending on user updating on page - post method become convoluted , if/else mess example: def post(self, request, *args, **kwargs): # handle user changing available date if request.post.get('availabledate') != none: self.updateavailabledate(request) return jsonresponse({'result':'success'}) if request.post.get('newprojectname') != none: creative_user = creativeuserprofile.objects.get(id = request.user.id) project = project.create_project(creative_user, request.post['newprojectname']) project.save() return jsonresponse({'projectid': project.id }) if request.post.get('projectimage') != none , request.files['file']: proj...
Read more

performance - Why is XCHG reg, reg a 3 micro-op instruction on modern Intel architectures? -

i'm doing micro-optimization on performance critical part of code , came across sequence of instructions (in at&t syntax): add %rax, %rbx mov %rdx, %rax mov %rbx, %rdx i thought had use case xchg allow me shave instruction , write: add %rbx, %rax xchg %rax, %rdx however, dimay found agner fog's instruction tables , xchg 3 micro-op instruction 2 cycle latency on sandy bridge, ivy bridge, broadwell, haswell , skylake. 3 whole micro-ops , 2 cycles of latency! 3 micro-ops throws off 4-1-1-1 cadence , 2 cycle latency makes worse original in best case since last 2 instructions in original might execute in parallel. now... cpu might breaking instruction micro-ops equivalent to: mov %rax, %tmp mov %rdx, %rax mov %tmp, %rdx where tmp anonymous internal register , suppose last 2 micro-ops run in parallel latency 2 cycles. given register renaming occurs on these micro-architectures, though, doesn't make sense me done way. why wouldn't register renamer ...
Read more

c# - Asp.net web api : redirect unauthorized requst to forbidden page -

im trying redirect unauthorized request forbidden page instead i'm getting forbidden page in response body , how can fix ? here's startup class : app.createperowincontext(storecontext.create); app.createperowincontext<applicationusermanager>(applicationusermanager.create); app.createperowincontext<applicationsigninmanager>(applicationsigninmanager.create); app.usecookieauthentication(new cookieauthenticationoptions { authenticationtype = defaultauthenticationtypes.applicationcookie, expiretimespan = timespan.fromdays(30), }); app.useexternalsignincookie(defaultauthenticationtypes.externalcookie); app.usetwofactorsignincookie(defaultauthenticationtypes.twofactorcookie, timespan.fromminutes(5)); app.usetwofactorrememberbrowsercookie(defaultauthenticationtypes.twofactorrememberbrowsercookie); the method im trying reach : [httpget] [authorize(roles = "admin")] public string getcurrentusername() { return userman...
Read more