• Become a Power user Intro to AutoHotkey Intermediate AutoHotkey Intermediate Objects GUIs are Easy w/AutoHotkey Painlessly switch from V1 to V2

Shading cells in Excel with AutoHotkey via COM

Shading cells in Excel with AutoHotkeyShading cells in Excel with AutoHotkey

Connecting to Excel with AutoHotkey is a breeze with COM.  Once you have a connection to the correct Excel object (in this case the worksheet) setting the cell shading is super easy!

In the below video I use the following function Shading cells in Excel with AutoHotkey

XL_Handle(XL,3) ;1=Application 2=Workbook 3=Worksheet
;0=clear 1=black 2=none 3=Red 4=Lt Grn 6=Brt Yel 7=Mag 8=brt blu 15=Grey 17=Lt purp  19=Lt Yel 20=Lt blu 22=Salm 26=Brt Pnk 28=Brt Blue
XL_Format_Cell_Shading(XL,RG:="A1:H10",Color:=0)

;***********Shading function******************* 
XL_Format_Cell_Shading(PXL,RG="",Color=0){
	PXL.Application.ActiveSheet.Range(RG).Interior.ColorIndex :=Color
}
;  Function for connecting to Excel
XL_Handle(ByRef PXL,Sel){
ControlGet, hwnd, hwnd, , Excel71, ahk_class XLMAIN ;identify the hwnd for Excel
IfEqual,Sel,1, Return, PXL:= ObjectFromWindow(hwnd,-16).application ;Handle to Excel Application
IfEqual,Sel,2, Return, PXL:= ObjectFromWindow(hwnd,-16).parent ;Handlle to active Workbook
IfEqual,Sel,3, Return, PXL:= ObjectFromWindow(hwnd,-16).activesheet ;Handle to Active Worksheet
}
;***********adapted from ACC.ahk*******************
ObjectFromWindow(hWnd, idObject = -4){
(if Not h)?h:=DllCall("LoadLibrary","Str","oleacc","Ptr")
 If DllCall("oleacc\AccessibleObjectFromWindow","Ptr",hWnd,"UInt",idObject&=0xFFFFFFFF,"Ptr",-VarSetCapacity(IID,16)+NumPut(idObject==0xFFFFFFF0?0x46000000000000C0:0x719B3800AA000C81,NumPut(idObject==0xFFFFFFF0?0x0000000000020400:0x11CF3C3D618736E0,IID,"Int64"),"Int64"), "Ptr*", pacc)=0
 Return ComObjEnwrap(9,pacc,1)
}

Fifty-six frequently used colors

Shading cells in Excel with AutoHotkey

Video demonstrating Shading cells in Excel with AutoHotkey

AutoHotkey Merchandise-White Stress ball

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.