Loop through the cells in an excel range and change the interior color if a condition is met

[silambua36]

Hi,

I have looked for this everywhere but I couldn't find it. So I'm posting here. As a part of a Lisp routine I'm trying to create a sub function which opens an excel file and selects a range in it. And then I want it loop through each cell in the range and check for it's interior color. If the color of a particular cell is "15849925", I want to change it to "16777215".

I was able to do this in vba using "For each cell in Range" statement. But I want to do this using VLISP. Here is what I got so far.

;;-------------------------------------------------------------------------------;;
;To set the color of a cell to 16777215 in a range, if the interior color of the cell is 15849925
(defun c:xc ()
(setq xlApp (vlax-get-or-create-object "excel.application"))
(setq xlWb (vlax-get-property xlApp "workbooks"))
(vlax-invoke-method xlWb "open" "D:\\example.xlsx")
(setq xlSheet (vlax-get-property xlApp "activesheet"))
(setq xlRange (vlax-get-property xlSheet "Range" "A9:J30"))

(;I want to do somethin here like the following

(foreach xlcell (xlRange)

     (if (= (vlax-get-property xlCell "color") "15849925")

               (vlax-put-property xlCell "color" "16777215")

)  ;end for each

;I know it's wrong. I don't know how to loop through each cells in the range.

)

(vlax-release-object xlRange)
(vlax-release-object xlSheet)
(vlax-invoke-method (vlax-get-property xlApp "Activeworkbook") 'Close :vlax-True)
(vlax-release-object xlWb)
(vlax-release-object xlApp)

);end defun

Thanks in advance.

Silambarasan A

[john.uhden · Accepted solution]

I'm no expert on this, but if xlrange is an object, not a list, then try using vlax-for instead of foreach.

[Sea-Haven · Accepted solution]

This is a get cell may need the variant value in your code for color.

(vlax-variant-value (vlax-get-property myRange 'Value2)))

[silambua36]

Thank you it worked.

;----------------------------------------------------------------------------
;To set the color of a cell to 16777215 in a range, if the interior color of the cell is 15849925
(defun sim:setnilcolor (fp / )
(setq xlApp (vlax-get-or-create-object "excel.application"))
(setq xlWb (vlax-get-property xlApp "workbooks"))
(vlax-invoke-method xlWb "open" fp); fp -file path
(setq xlSheet (vlax-get-property xlApp "activesheet"))
(setq xlRange (vlax-get-property xlSheet "Range" "A9:J90"))

(vlax-for item xlRange

(setq int (vlax-get-property item "interior"))
(if (= (setq incolor (rtos (vlax-variant-value (vlax-get-property int "color")) 2 0)) "15849925")

(vlax-put-property int "color" "16777215")
);end if

) ;end for vlax-for

(vlax-release-object xlRange)
(vlax-release-object xlSheet)
(vlax-invoke-method (vlax-get-property xlApp "Activeworkbook") 'Close :vlax-True)
(vlax-release-object xlWb)
(vlax-release-object xlApp)

);end defun

[silambua36]

Thank you. I was having trouble in the if statement because of this. And it is clear

[Sea-Haven]

I am sure some one will have a list of the variable names 'Value2 'Interior would be handy to know where to look them up. Anyone ?