Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Find Coloured Font in Excel Cell
#1
Greetings,

I have written the below code to check if the cell font colour in excel is red then do some routine. However, I am stuck on how to check the colour in excel via QM.

Macro NC VCC Release Corrections
Code:
Copy      Help
;;Declare Variables
ARRAY(str) removeNamesList, SANamesCheckList
int counter1, counter2, SANamesCheckListCnt

int w1=act(win("Microsoft Excel - LR Sales" "XLMAIN")) ;;Obtain the particular sales file handle and activate it.
ExcelSheet esData.Init("Data") ;;Initialize an ExcelSheet object
ExcelSheet esVCC.Init("VCC Release Spool") ;;Initialize an ExcelSheet object

;;Initialize all variables to be sure
counter1=0
counter2=0
SANamesCheckListCnt=0

;Obtain SA Name list(dynamic ranges) into arrays from 'VCC Rlease Sheet' - To obtain count of dynamic records
esVCC.CellsToArray(SANamesCheckList "G:G") ;;Obtain list of SA name from 'VCC Release Sheet'
SANamesCheckList.ubound

;arrayCheckRoutine
;;Obtain count of actual records - not counting the blanks
for counter1 1 SANamesCheckList.len
,if SANamesCheckList.len<>0 ;;Skips checking of empty cells
,,SANamesCheckListCnt=SANamesCheckListCnt+1

counter1 = 0 ;; Reset counter for re-use

;obtainSANameFromAL

;;Check Autoline system for the correct SA Name and replace in Excel
esVCC.SelectCell("G1") ;;Select Header Cell of SA Names

for counter1 1 SANamesCheckListCnt
,esVCC.CellsToArray(SANamesCheckList "G" counter1)
,if esVCC.
Best Regards,
Philip
#2
Macro Macro2627
Code:
Copy      Help
ExcelSheet es.Init
Excel.Range r=es._Range("A1")
int fillColor=r.Interior.Color
int fontColor=r.Font.Color
out F"fillColor=0x{fillColor}, fontColor=0x{fontColor}"
#3
I improvised on the code you provided to suit my need as below.
However, the code does not detect the red coloured font in column G of Excel.
The colour comes from conditional formatting.
Kindly advise where I am going wrong:

Macro NC VCC Release Corrections
Code:
Copy      Help
;obtainSANameFromAL

;;Check Autoline system for the correct SA Name and replace in Excel
esVCC.SelectCell("G1") ;;Select Header Cell of SA Names

for counter1 1 SANamesCheckListCnt
,r=esVCC._Range(ExcelRange(7 counter1)) ;;Checks each row on column G
,if r.Font.Color=255
,,out "yes"
,,out counter1
,,;;Obtain the stock# from same row
,else
,,out "not red"
,,out counter1
Best Regards,
Philip
#4
Maybe some cells use ColorIndex instead of Color, I don't know.
out r.Font.Color
out r.Font.ColorIndex
#5
Got It.

This is the solution:

Macro NC VCC Release Corrections
Code:
Copy      Help
,if r.DisplayFormat.Font.Color=255
Best Regards,
Philip


Forum Jump:


Users browsing this thread: 1 Guest(s)