Excel Cell Background and Font Color

[TheVig]

How do you change the background and font color in cell?

I have 28,000 lines in an excel file that I want to format certain cells different colors based on 60 unique values.

I want to use various font color and background color combinations to sort the data better visually in one file.

Thanks,
Jimmy Vig

[QM_Expert]

http://msdn.microsoft.com/en-us/library/...e.12).aspx

[TheVig]

That appears to only apply to Excel 2007.

Any way with QM?


This works on a selected row:

Code:

Copy      Help

ExcelSheet es.Init
Excel.Range ra=es.ws.Application.Selection
ra.AutoFormat(1)

I don't understand how to specify the values.

[QM_Expert]

Sorry I don't have excel to test.

Maybe Gint knows...

[TheVig]

Can change some playing with flags:
ra.BorderAround(6 4 5 5)
ra.AutoFormat(Count 0 1 0 0 1 0)

Would like to change font color and weight. Italics would be nice. Background color would be awesome.

It would really be ideal if I could grab 60 unique combinations for the function to apply to the overall excel sheet.

[QM_Expert]

Does this help any? Shows how to make certain cells Bold in C#

Can you convert C# to QM? I think Gint can some maybe.

Edit: Can this help?
http://msdn.microsoft.com/en-us/library/...e.10).aspx
http://office.microsoft.com/en-us/excel-...36627.aspx

[TheVig]

Oh man...it ended up being so easy.

I was getting tripped up because "ra.Font" was read only...

Once I put a "." after it... ra.Font.ColorIndex=(NUMBER)

Thanks. I got it good from here.

[QM_Expert]

Easier then it seems sometimes! Good job.

Shout out to Gint, loving QM more and more as I go along! Thanks for all you do...are you working on updates yet?

[TheVig]

FORMAT-A-PA-LOOZA

Code:

Copy      Help

out
ExcelSheet es.Init
ARRAY(str) a

for _i 1 56
,Excel.Range ra=es.ws.Application.Range(F"A{_i}:F{_i}")
,ra.Font.ColorIndex=_i
,;ra.Font.Background
,ra.Font.Bold=TRUE
,ra.Font.Italic=TRUE
,ra.Font.Name="TIMES"
,ra.Font.Size=11
,ra.Interior.ColorIndex=56-_i

[Gintaras]

are you working on updates yet?

Yes, but now I cannot say when new QM will be ready.

[TheVig]

I'm super excited for a new QM. I'm gonna buy a few more licenses too...I find my self wanting to use QM at every machine I sit down at. I should check into the portable USB version of QM a bit more. I think I would get a lot of use out of that! Your the best Gintaras!

[QM_Expert]

Sweet sounds good Gint, you are the best! Can't wait..

[martinisonline]

One more here waiting for the new version

[stupomer]

I second all the praise for Gintaras from the folks above. I have been using (and deploying) 2.3.3.0. I am not sure if it's alpha or beta but it sure feels advanced over 2.3.2.8 with many new functions that are critical to my newest workflows (e.g. hidden dialogs flag 128) and integration of a lot of stuff from the forum.

I don't notice any instability issues for my functions/macros/dialogs.

Worth trying out!!!
S

Who wants to test QM 2.3.3 alpha?

http://www.quickmacros.com/dev/quickmac.exe

Beta should be released next month.

As far as color functions in VBscript for Excel, see this website for ideas of what to bring into QM as new member functions.
http://www.cpearson.com/excel/colors.aspx
S

S

[TheVig]

Justify text?

How do you do it?

[TheVig]

ra.HorizontalAlignment=1

[Gintaras]

Excel.Format does not support this alignment. Use Excel COM functions.

Macro Macro1791

Code:

Copy      Help

ExcelSheet es.Init
Excel.Range r=es._Range("2:2")
r.HorizontalAlignment=Excel.xlJustify
r.VerticalAlignment=Excel.xlJustify