Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc,microsoft.public.excel.newusers,microsoft.public.excel.programming
|
|||
|
|||
Cell right next to colored cells is automatically colored on entering a value
Hello,
I recently started to program in VBA for Excel. I'm using Excel 2000 (9.0.4402 SR-1) on Windows XP. The formatting from all the cells in the workbook and worksheet is General (Right-click on a cell, Format cells, then tab Number, Category-listbox: General) When I put a commandbutton CommandButton1 on the first worksheet of my workbook and I put the following code in the CommandButton1-onclick-event. ------------------- Option Explicit Private Sub CommandButton1_Click() Dim WeekdayNames(0 To 6) As String Dim WeekdayColors(0 To 6) As Long WeekdayNames(0) = "monday" WeekdayNames(1) = "tuesday" WeekdayNames(2) = "wednesday" WeekdayNames(3) = "thursday" WeekdayNames(4) = "friday" WeekdayNames(5) = "saturday" WeekdayNames(6) = "sunday" WeekdayColors(0) = RGB(255, 0, 0) ' red WeekdayColors(1) = RGB(255, 0, 0) ' red WeekdayColors(2) = RGB(255, 0, 0) ' red WeekdayColors(3) = RGB(255, 0, 0) ' red WeekdayColors(4) = RGB(255, 0, 0) ' red WeekdayColors(5) = RGB(0, 0, 255) ' blue WeekdayColors(6) = RGB(0, 0, 255) ' blue Dim i As Integer For i = 0 To 6 ThisWorkbook.Worksheets(1).Range("A1").Offset(0, i).Interior.Color = WeekdayColors(i) Next i Dim TempRange As Range Set TempRange = ThisWorkbook.Worksheets(1).Range("A1:G1") TempRange.Value = WeekdayNames End Sub -------------------- When I click on the button the code is executed without any errors. The code writes the name of the day in the cells A1 till G1 and it colors the cells with the workdays (monday till friday) as red and the cells with the days of the weekend (saturday and sunday) as blue. No problem until so far. !!! But when I select cell H1 and a enter a character (for example 'm' or 'j') cell H1 is also colored red. And when I select cell I1 and a enter a character, cell I1 is also colored red. And when I select J1 and enter a value, J1 is colored red..... and so on. When I enter a number into cell H1, nothing happens, the color of H1 does not change. Then I select I1 and enter a character, nothing happens, the color of I1 does not change. It looks like there happens some kind of auto-fill for the colors of the cells. My questions: 1) Why does the color of H1,I1, .... changes, when I enter a character? Maybe because the formatting of the cells is General and not Text? Can I prevent this "auto-coloring" ? 2) How do I change the formatting of a cel by VBA-code? For example: change from General to Text 3) TempRange.Value = WeekdayNames automatically fills a range with the array-values Can i make an array of RGB-values and assign this array to the background colors of a range Like this: Dim WeekColors(0 To 6) As Long Colors(0) = RGB(0,255,255) ... = ......... Colors(6) = RGB(255,128, 0) TempRange.Interior.Color = WeekColors kind regards, Johan De Schutter Software designer mailto: Nisus nv Antwerpsesteenweg 107, B-2390 Oostmalle Tel: +32(0)3/312.92.30 Please visit our website: http://www.nisus.be |
#2
Posted to microsoft.public.excel.misc,microsoft.public.excel.newusers,microsoft.public.excel.programming
|
|||
|
|||
Cell right next to colored cells is automatically colored on entering a value
Try unchecking the Tools/Options/Edit/Extend lists and formulas
checkbox. In article , "Johan De Schutter" <jdsATDOTnisusDOTbe wrote: When I click on the button the code is executed without any errors. The code writes the name of the day in the cells A1 till G1 and it colors the cells with the workdays (monday till friday) as red and the cells with the days of the weekend (saturday and sunday) as blue. No problem until so far. !!! But when I select cell H1 and a enter a character (for example 'm' or 'j') cell H1 is also colored red. And when I select cell I1 and a enter a character, cell I1 is also colored red. And when I select J1 and enter a value, J1 is colored red..... and so on. When I enter a number into cell H1, nothing happens, the color of H1 does not change. Then I select I1 and enter a character, nothing happens, the color of I1 does not change. It looks like there happens some kind of auto-fill for the colors of the cells. |
#3
Posted to microsoft.public.excel.misc,microsoft.public.excel.newusers,microsoft.public.excel.programming
|
|||
|
|||
Cell right next to colored cells is automatically colored on entering a value
Sounds like you have conditional formatting set up for those cells.
Select all the cells and do Edit=Goto=Special and select Conditional Format If any cells are selected, then you have conditional formatting in those cells. With those cells still selected to Format=Conditional formatting and use the delete button to remove it. -- Regards, Tom Ogilvy "Johan De Schutter" <jdsATDOTnisusDOTbe wrote in message .be... Hello, I recently started to program in VBA for Excel. I'm using Excel 2000 (9.0.4402 SR-1) on Windows XP. The formatting from all the cells in the workbook and worksheet is General (Right-click on a cell, Format cells, then tab Number, Category-listbox: General) When I put a commandbutton CommandButton1 on the first worksheet of my workbook and I put the following code in the CommandButton1-onclick-event. ------------------- Option Explicit Private Sub CommandButton1_Click() Dim WeekdayNames(0 To 6) As String Dim WeekdayColors(0 To 6) As Long WeekdayNames(0) = "monday" WeekdayNames(1) = "tuesday" WeekdayNames(2) = "wednesday" WeekdayNames(3) = "thursday" WeekdayNames(4) = "friday" WeekdayNames(5) = "saturday" WeekdayNames(6) = "sunday" WeekdayColors(0) = RGB(255, 0, 0) ' red WeekdayColors(1) = RGB(255, 0, 0) ' red WeekdayColors(2) = RGB(255, 0, 0) ' red WeekdayColors(3) = RGB(255, 0, 0) ' red WeekdayColors(4) = RGB(255, 0, 0) ' red WeekdayColors(5) = RGB(0, 0, 255) ' blue WeekdayColors(6) = RGB(0, 0, 255) ' blue Dim i As Integer For i = 0 To 6 ThisWorkbook.Worksheets(1).Range("A1").Offset(0, i).Interior.Color = WeekdayColors(i) Next i Dim TempRange As Range Set TempRange = ThisWorkbook.Worksheets(1).Range("A1:G1") TempRange.Value = WeekdayNames End Sub -------------------- When I click on the button the code is executed without any errors. The code writes the name of the day in the cells A1 till G1 and it colors the cells with the workdays (monday till friday) as red and the cells with the days of the weekend (saturday and sunday) as blue. No problem until so far. !!! But when I select cell H1 and a enter a character (for example 'm' or 'j') cell H1 is also colored red. And when I select cell I1 and a enter a character, cell I1 is also colored red. And when I select J1 and enter a value, J1 is colored red..... and so on. When I enter a number into cell H1, nothing happens, the color of H1 does not change. Then I select I1 and enter a character, nothing happens, the color of I1 does not change. It looks like there happens some kind of auto-fill for the colors of the cells. My questions: 1) Why does the color of H1,I1, .... changes, when I enter a character? Maybe because the formatting of the cells is General and not Text? Can I prevent this "auto-coloring" ? 2) How do I change the formatting of a cel by VBA-code? For example: change from General to Text 3) TempRange.Value = WeekdayNames automatically fills a range with the array-values Can i make an array of RGB-values and assign this array to the background colors of a range Like this: Dim WeekColors(0 To 6) As Long Colors(0) = RGB(0,255,255) ... = ......... Colors(6) = RGB(255,128, 0) TempRange.Interior.Color = WeekColors kind regards, Johan De Schutter Software designer mailto: Nisus nv Antwerpsesteenweg 107, B-2390 Oostmalle Tel: +32(0)3/312.92.30 Please visit our website: http://www.nisus.be |
#4
Posted to microsoft.public.excel.misc,microsoft.public.excel.newusers,microsoft.public.excel.programming
|
|||
|
|||
Cell right next to colored cells is automatically colored on entering a value
Thanks for the answer,
I have no conditional formatting enabled for any cells on my sheet, and i don't want to use conditional formatting. I just want to use VBA to color the 7 cells in whatever color I want, without the auto-coloring in the cell right next to 7 colored cells. kind regards, Johan De Schutter Software designer mailto: Nisus nv Antwerpsesteenweg 107, B-2390 Oostmalle Tel: +32(0)3/312.92.30 Please visit our website: http://www.nisus.be "Tom Ogilvy" wrote in message ... Sounds like you have conditional formatting set up for those cells. Select all the cells and do Edit=Goto=Special and select Conditional Format If any cells are selected, then you have conditional formatting in those cells. With those cells still selected to Format=Conditional formatting and use the delete button to remove it. -- Regards, Tom Ogilvy "Johan De Schutter" <jdsATDOTnisusDOTbe wrote in message .be... Hello, I recently started to program in VBA for Excel. I'm using Excel 2000 (9.0.4402 SR-1) on Windows XP. The formatting from all the cells in the workbook and worksheet is General (Right-click on a cell, Format cells, then tab Number, Category-listbox: General) When I put a commandbutton CommandButton1 on the first worksheet of my workbook and I put the following code in the CommandButton1-onclick-event. ------------------- Option Explicit Private Sub CommandButton1_Click() Dim WeekdayNames(0 To 6) As String Dim WeekdayColors(0 To 6) As Long WeekdayNames(0) = "monday" WeekdayNames(1) = "tuesday" WeekdayNames(2) = "wednesday" WeekdayNames(3) = "thursday" WeekdayNames(4) = "friday" WeekdayNames(5) = "saturday" WeekdayNames(6) = "sunday" WeekdayColors(0) = RGB(255, 0, 0) ' red WeekdayColors(1) = RGB(255, 0, 0) ' red WeekdayColors(2) = RGB(255, 0, 0) ' red WeekdayColors(3) = RGB(255, 0, 0) ' red WeekdayColors(4) = RGB(255, 0, 0) ' red WeekdayColors(5) = RGB(0, 0, 255) ' blue WeekdayColors(6) = RGB(0, 0, 255) ' blue Dim i As Integer For i = 0 To 6 ThisWorkbook.Worksheets(1).Range("A1").Offset(0, i).Interior.Color = WeekdayColors(i) Next i Dim TempRange As Range Set TempRange = ThisWorkbook.Worksheets(1).Range("A1:G1") TempRange.Value = WeekdayNames End Sub -------------------- When I click on the button the code is executed without any errors. The code writes the name of the day in the cells A1 till G1 and it colors the cells with the workdays (monday till friday) as red and the cells with the days of the weekend (saturday and sunday) as blue. No problem until so far. !!! But when I select cell H1 and a enter a character (for example 'm' or 'j') cell H1 is also colored red. And when I select cell I1 and a enter a character, cell I1 is also colored red. And when I select J1 and enter a value, J1 is colored red..... and so on. When I enter a number into cell H1, nothing happens, the color of H1 does not change. Then I select I1 and enter a character, nothing happens, the color of I1 does not change. It looks like there happens some kind of auto-fill for the colors of the cells. My questions: 1) Why does the color of H1,I1, .... changes, when I enter a character? Maybe because the formatting of the cells is General and not Text? Can I prevent this "auto-coloring" ? 2) How do I change the formatting of a cel by VBA-code? For example: change from General to Text 3) TempRange.Value = WeekdayNames automatically fills a range with the array-values Can i make an array of RGB-values and assign this array to the background colors of a range Like this: Dim WeekColors(0 To 6) As Long Colors(0) = RGB(0,255,255) ... = ......... Colors(6) = RGB(255,128, 0) TempRange.Interior.Color = WeekColors kind regards, Johan De Schutter Software designer mailto: Nisus nv Antwerpsesteenweg 107, B-2390 Oostmalle Tel: +32(0)3/312.92.30 Please visit our website: http://www.nisus.be |
#5
Posted to microsoft.public.excel.misc,microsoft.public.excel.newusers,microsoft.public.excel.programming
|
|||
|
|||
Cell right next to colored cells is automatically colored on entering a value
Thanks for the answer,
Yep,this works. When i uncheck the checkbox, the right cell next to colored cells isn't colored. How can i uncheck this checkbox, or the underlying setting in Excel, using VBA-code? My other questions: 2) How do I change the formatting of a cel by VBA-code? For example: change from General to Text 3) Dim WeekdayNames(0 To 6) As String Dim TempRange As Range Set TempRange = ThisWorkbook.Worksheets(1).Range("A1:G1") TempRange.Value = WeekdayNames the last statement automatically fills a range with the array-values. Can i use a similar approach to assign colors from an array of Long to the background of a certain Range? Like this: Dim WeekColors(0 To 6) As Long Colors(0) = RGB(0,255,255) ... = ......... Colors(6) = RGB(255,128, 0) TempRange.Interior.Color = WeekColors ' but the last statement does not work. the whole background is colored with the first color in the array Colors ' i want that the background of cell 0 in the TempRange is colored with Colors(0), cell 1 with Colors(1), etc.... kind regards, Johan De Schutter Software designer mailto: Nisus nv Antwerpsesteenweg 107, B-2390 Oostmalle Tel: +32(0)3/312.92.30 Please visit our website: http://www.nisus.be ------------------------ "J.E. McGimpsey" wrote in message ... Try unchecking the Tools/Options/Edit/Extend lists and formulas checkbox. In article , "Johan De Schutter" <jdsATDOTnisusDOTbe wrote: When I click on the button the code is executed without any errors. The code writes the name of the day in the cells A1 till G1 and it colors the cells with the workdays (monday till friday) as red and the cells with the days of the weekend (saturday and sunday) as blue. No problem until so far. !!! But when I select cell H1 and a enter a character (for example 'm' or 'j') cell H1 is also colored red. And when I select cell I1 and a enter a character, cell I1 is also colored red. And when I select J1 and enter a value, J1 is colored red..... and so on. When I enter a number into cell H1, nothing happens, the color of H1 does not change. Then I select I1 and enter a character, nothing happens, the color of I1 does not change. It looks like there happens some kind of auto-fill for the colors of the cells. |
#6
Posted to microsoft.public.excel.misc,microsoft.public.excel.newusers,microsoft.public.excel.programming
|
|||
|
|||
Cell right next to colored cells is automatically colored on entering a value
Sometimes the best way to find out how to do something in VBA is to
turn on the macro recorder. In article , "Johan De Schutter" <jdsATDOTnisusDOTbe wrote: Yep,this works. When i uncheck the checkbox, the right cell next to colored cells isn't colored. How can i uncheck this checkbox, or the underlying setting in Excel, using VBA-code? |
#7
Posted to microsoft.public.excel.misc,microsoft.public.excel.newusers,microsoft.public.excel.programming
|
|||
|
|||
Cell right next to colored cells is automatically colored on entering a value
one way:
Range("A1").NumberFormat = "@" In article , "Johan De Schutter" <jdsATDOTnisusDOTbe wrote: 2) How do I change the formatting of a cel by VBA-code? For example: change from General to Text |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 - cell borders not visible when cells colored | Excel Discussion (Misc queries) | |||
VBA to count colored cells | Excel Worksheet Functions | |||
colored cells | Excel Discussion (Misc queries) | |||
Colored and filtered cells | New Users to Excel | |||
sum colored cells | Excel Discussion (Misc queries) |