How to change the cell color of a range in ms excel? Topic is solved

[Sabestian Caine]

I am trying to change the color of cells range a1:m1 from white to black. For that i tried to use these codes but these codes are not working-

Code: Select all

f1::
xl := ComObjActive("Excel.Application")
ab:= xl.range("a1:m1").selection
ab.Selection.Interior
        ab.Pattern = xlSolid
        ab.PatternColorIndex = xlAutomatic
        ab.ThemeColor = xlThemeColorLight1
        ab.TintAndShade = 0
        ab.PatternTintAndShade = 0
        return

You can see this image for what i want-


Please help me...
thanks a lot...

[Sabestian Caine]

i used these codes also by which i can select the range a1:m1 only-

Code: Select all

f2::
xl := ComObjActive("Excel.Application")
ab:= xl.range("a1:m1").select
        ab.Pattern := xlSolid
        ab.PatternColorIndex := xlAutomatic
        ab.ThemeColor := xlThemeColorLight1
        ab.TintAndShade := 0
        ab.PatternTintAndShade := 0
        return

Please help me....

[Nightwolf85]

I don't have a direct answer for you right now, but looking at your code it doesn't appear you are opening a workbook, or connecting to the active one. Once you select a workbook you then need to pick a worksheet to work on.

I believe you can do:

Code: Select all

xl := ComObjActive("Excel.Application")
worksheet := xl.ActiveWorkbook.WorkSheets(1) ; Select Worksheet 1 of the current Excel document
ab := worksheet.range("a1:m1").select

That will at least select the cells for you. If you still need help coloring the cells let me know and I will work, with you, to get what you need working.

**Edit
It also looks like you are trying to use Excel constants, and unless you define them somewhere else in your script they won't work, you need to use the numbers they correspond to.

[Sabestian Caine]

I don't have a direct answer for you right now, but looking at your code it doesn't appear you are opening a workbook, or connecting to the active one. Once you select a workbook you then need to pick a worksheet to work on.

I believe you can do:

Code: Select all

xl := ComObjActive("Excel.Application")
worksheet := xl.ActiveWorkbook.WorkSheets(1) ; Select Worksheet 1 of the current Excel document
ab := worksheet.range("a1:m1").select

That will at least select the cells for you. If you still need help coloring the cells let me know and I will work, with you, to get what you need working.

**Edit
It also looks like you are trying to use Excel constants, and unless you define them somewhere else in your script they won't work, you need to use the numbers they correspond to.

Dear Nightwolf85......thanks for your reply....i just want to change the color of range a1:m1. By default the color of entire sheet is white but i want this range's color should get black when i press f1 key. For this i want to use com interface codes. when i record a macro in excel vba then it shows these codes for turning the range a1:m1 from white to black but i am unable to convert these codes into com interface codes-

Code: Select all

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("A1:M1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

Please help me...

Thanks a lot...

[FanaticGuru]

Code: Select all

F1::
	Xl := ComObjActive("Excel.Application")
	if (Toggle := !Toggle)
		Xl.Range("A1:M1").Interior.Color := 0 ; Fill Color Black
	else
		Xl.Range("A1:M1").Interior.Pattern := 0 ; No Fill
return

No Fill and Fill with white is a little different. Any time a cell has a Fill color the grey border grid lines are removed so Fill with Color white (Xl.Range("A1:M1").Interior.Color := 16777215) would make the cell white but also removes the grid lines which is different than no Fill at all.

FG

[Nightwolf85]

If you combine what I posted earlier with what FanaticGuru posted, you should be able to come up with what you want.

If you want it to turn Black and have no way to go back then:

Code: Select all

F1::
xl := ComObjActive("Excel.Application")
xl.ActiveWorkbook.ActiveSheet.Range("a1:m1").Interior.Color := 0x000000  ; Select Worksheet 1 of the current Excel document. Color is in BGR format meaning 0x0000FF is Red.
xl := ""
Return

If you want to toggle it then:

Code: Select all

F1::
xl := ComObjActive("Excel.Application")
IF (Toggle := !Toggle)
	xl.ActiveWorkbook.ActiveSheet.Range("a1:m1").Interior.Color := 0x000000  ; Select Worksheet 1 of the current Excel document. Color is in BGR format meaning 0x0000FF is Red.
Else
	xl.ActiveWorkbook.ActiveSheet.Range("a1:m1").Interior.Pattern := 0 ; Select Worksheet 1 of the current Excel document
xl := ""
Return

Hope that helps you understand what you need to do. Check out the Excel COM object model site for more: https://msdn.microsoft.com/en-us/librar ... 94068.aspx

**Edit
For some reason with my testing the first run of the HotKey will throw an error, but then works fine on subsequent presses. This only happens if excel is opened after the script is running, if the script is started after excel it doesn't appear to happen at all. I found out the reason is because Office products don't register their objects until they lose focus? and you can't connect until its registered. Sure enough if I unfocus Excel then run the HotKey it works without the error, so nothing wrong with the code at all then. http://stackoverflow.com/questions/1502 ... 0x800401e3

Also since it doesn't appear you are keeping the xl reference around you could just do:

Code: Select all

F1::ComObjActive("Excel.Application").ActiveWorkbook.ActiveSheet.Range("A1:M1").Interior.Color := 0x000000

OR

Code: Select all

F1::
IF (Toggle := !Toggle)
	ComObjActive("Excel.Application").ActiveWorkbook.ActiveSheet.Range("A1:M1").Interior.Color := 0x000000  ; Select Worksheet 1 of the current Excel document. Color is in BGR format meaning 0x0000FF is Red.
Else
	ComObjActive("Excel.Application").ActiveWorkbook.ActiveSheet.Range("a1:m1").Interior.Pattern := 0 ; Select Worksheet 1 of the current Excel document

Return

[Sabestian Caine]

Code: Select all

F1::
	Xl := ComObjActive("Excel.Application")
	if (Toggle := !Toggle)
		Xl.Range("A1:M1").Interior.Color := 0 ; Fill Color Black
	else
		Xl.Range("A1:M1").Interior.Pattern := 0 ; No Fill
return

No Fill and Fill with white is a little different. Any time a cell has a Fill color the grey border grid lines are removed so Fill with Color white (Xl.Range("A1:M1").Interior.Color := 16777215) would make the cell white but also removes the grid lines which is different than no Fill at all.

FG

Thanks FanaticGuru.....
you always rock.....
this is what i want...
thanks a lot....

[Sabestian Caine]

If you combine what I posted earlier with what FanaticGuru posted, you should be able to come up with what you want.

If you want it to turn Black and have no way to go back then:

Code: Select all

F1::
xl := ComObjActive("Excel.Application")
xl.ActiveWorkbook.ActiveSheet.Range("a1:m1").Interior.Color := 0x000000  ; Select Worksheet 1 of the current Excel document. Color is in BGR format meaning 0x0000FF is Red.
xl := ""
Return

If you want to toggle it then:

Code: Select all

F1::
xl := ComObjActive("Excel.Application")
IF (Toggle := !Toggle)
	xl.ActiveWorkbook.ActiveSheet.Range("a1:m1").Interior.Color := 0x000000  ; Select Worksheet 1 of the current Excel document. Color is in BGR format meaning 0x0000FF is Red.
Else
	xl.ActiveWorkbook.ActiveSheet.Range("a1:m1").Interior.Pattern := 0 ; Select Worksheet 1 of the current Excel document
xl := ""
Return

Hope that helps you understand what you need to do. Check out the Excel COM object model site for more: https://msdn.microsoft.com/en-us/librar ... 94068.aspx

**Edit
For some reason with my testing the first run of the HotKey will throw an error, but then works fine on subsequent presses. This only happens if excel is opened after the script is running, if the script is started after excel it doesn't appear to happen at all. I found out the reason is because Office products don't register their objects until they lose focus? and you can't connect until its registered. Sure enough if I unfocus Excel then run the HotKey it works without the error, so nothing wrong with the code at all then. http://stackoverflow.com/questions/1502 ... 0x800401e3

Also since it doesn't appear you are keeping the xl reference around you could just do:

Code: Select all

F1::ComObjActive("Excel.Application").ActiveWorkbook.ActiveSheet.Range("A1:M1").Interior.Color := 0x000000

OR

Code: Select all

F1::
IF (Toggle := !Toggle)
	ComObjActive("Excel.Application").ActiveWorkbook.ActiveSheet.Range("A1:M1").Interior.Color := 0x000000  ; Select Worksheet 1 of the current Excel document. Color is in BGR format meaning 0x0000FF is Red.
Else
	ComObjActive("Excel.Application").ActiveWorkbook.ActiveSheet.Range("a1:m1").Interior.Pattern := 0 ; Select Worksheet 1 of the current Excel document

Return

Thanks dear Nightwolf85...
thanks a lot for your support.....
Have a great time....