Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro: Changing Color of Cell
I am anything but an Excel or VB programmer. I knew I needed a macro and using some others I found on the web I created the following: ============================================== Sub auto_open() ' Run the macro DidCellsChange any time a entry is made in a ' cell in Sheet1. ThisWorkbook.Worksheets("Sheet1").OnEntry = "DidCellsChange" End Sub Sub DidCellsChange() Dim KeyCells As String ' Define which cells should trigger the KeyCellsChanged macro. KeyCells = "C45, E45, H45, I45, M45, B47" ' If the Activecell is one of the key cells, call the ' KeyCellsChanged macro. If Not Application.Intersect(ActiveCell, Range(KeyCells)) _ Is Nothing Then KeyCellsChanged End Sub Sub KeyCellsChanged() Dim Cell As Object ' If the values in C45,E45,H45,M45,B47 are greater than nil... For Each Cell In Range("C45, E45, H45, M45, B47") If Cell "" Then ' Make the background color of the cell the 3rd color on the ' current palette. Cell.Interior.ColorIndex = 3 Else ' Otherwise, set the background to none (default). Cell.Interior.ColorIndex = 0 End If Next Cell =========================================== How is it possible to have the background color in cell A44 change to Cell.Interior.ColorIndex = 3 instead of cell C45, E45, H45, M45, or B47? I thought it would be as simple as changing line Cell.Interior.ColorIndex = 3 to Cell.Interior.ColorIndex("A44") = 3 but this causes the macro to crash and the debugger comes up. Again, any assistance would be greatly appreciated. Thank you all in advance. Kevin Knight -- kevinknight09 ------------------------------------------------------------------------ kevinknight09's Profile: http://www.excelforum.com/member.php...o&userid=37024 View this thread: http://www.excelforum.com/showthread...hreadid=567475 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro: Changing Color of Cell
Kevin,
Range("A44").Interior.ColorIndex = 3 BUT (and this is a big BUT) you could also use the built-in functionality of Conditional Formattting. Much easier in the long run, especially if cells, columns, or rows can be inserted, deleted, or moved. Far better, given the simple conditional used. HTH, Bernie MS Excel MVP "kevinknight09" wrote in message news:kevinknight09.2bx3lf_1154530218.7559@excelfor um-nospam.com... I am anything but an Excel or VB programmer. I knew I needed a macro and using some others I found on the web I created the following: ============================================== Sub auto_open() ' Run the macro DidCellsChange any time a entry is made in a ' cell in Sheet1. ThisWorkbook.Worksheets("Sheet1").OnEntry = "DidCellsChange" End Sub Sub DidCellsChange() Dim KeyCells As String ' Define which cells should trigger the KeyCellsChanged macro. KeyCells = "C45, E45, H45, I45, M45, B47" ' If the Activecell is one of the key cells, call the ' KeyCellsChanged macro. If Not Application.Intersect(ActiveCell, Range(KeyCells)) _ Is Nothing Then KeyCellsChanged End Sub Sub KeyCellsChanged() Dim Cell As Object ' If the values in C45,E45,H45,M45,B47 are greater than nil... For Each Cell In Range("C45, E45, H45, M45, B47") If Cell "" Then ' Make the background color of the cell the 3rd color on the ' current palette. Cell.Interior.ColorIndex = 3 Else ' Otherwise, set the background to none (default). Cell.Interior.ColorIndex = 0 End If Next Cell =========================================== How is it possible to have the background color in cell A44 change to Cell.Interior.ColorIndex = 3 instead of cell C45, E45, H45, M45, or B47? I thought it would be as simple as changing line Cell.Interior.ColorIndex = 3 to Cell.Interior.ColorIndex("A44") = 3 but this causes the macro to crash and the debugger comes up. Again, any assistance would be greatly appreciated. Thank you all in advance. Kevin Knight -- kevinknight09 ------------------------------------------------------------------------ kevinknight09's Profile: http://www.excelforum.com/member.php...o&userid=37024 View this thread: http://www.excelforum.com/showthread...hreadid=567475 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro: Changing Color of Cell
Thank you!!!! -- kevinknight09 ------------------------------------------------------------------------ kevinknight09's Profile: http://www.excelforum.com/member.php...o&userid=37024 View this thread: http://www.excelforum.com/showthread...hreadid=567475 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro: Changing Color of Cell
Would you be willing to share the Conditional Formatting that you referenced? Again I apologize for my lack of Excel and VB knowledge. Thank you! -- kevinknight09 ------------------------------------------------------------------------ kevinknight09's Profile: http://www.excelforum.com/member.php...o&userid=37024 View this thread: http://www.excelforum.com/showthread...hreadid=567475 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro: Changing Color of Cell
Kevin,
Select cell A44, for example. Then from the main Excel menu, choose Format / Conditional Formatting.... and choose "Cell Value is.." and then one of the conditionals, and select your limit or condition. Then set the format that you want by clicking the format button. Look in help for examples. HTH, Bernie MS Excel MVP "kevinknight09" wrote in message news:kevinknight09.2bx7rf_1154535607.6597@excelfor um-nospam.com... Would you be willing to share the Conditional Formatting that you referenced? Again I apologize for my lack of Excel and VB knowledge. Thank you! -- kevinknight09 ------------------------------------------------------------------------ kevinknight09's Profile: http://www.excelforum.com/member.php...o&userid=37024 View this thread: http://www.excelforum.com/showthread...hreadid=567475 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro: Changing Color of Cell
Again, thank you! -- kevinknight09 ------------------------------------------------------------------------ kevinknight09's Profile: http://www.excelforum.com/member.php...o&userid=37024 View this thread: http://www.excelforum.com/showthread...hreadid=567475 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro: Changing Color of Cell
I am not sure my question is relevant to your discussion. I am new here. I
want a font color to follow the numbers and or text when i reference that cell on another spreadsheet. How do i do that? "Bernie Deitrick" wrote: Kevin, Select cell A44, for example. Then from the main Excel menu, choose Format / Conditional Formatting.... and choose "Cell Value is.." and then one of the conditionals, and select your limit or condition. Then set the format that you want by clicking the format button. Look in help for examples. HTH, Bernie MS Excel MVP "kevinknight09" wrote in message news:kevinknight09.2bx7rf_1154535607.6597@excelfor um-nospam.com... Would you be willing to share the Conditional Formatting that you referenced? Again I apologize for my lack of Excel and VB knowledge. Thank you! -- kevinknight09 ------------------------------------------------------------------------ kevinknight09's Profile: http://www.excelforum.com/member.php...o&userid=37024 View this thread: http://www.excelforum.com/showthread...hreadid=567475 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Macro: Changing Color of Cell
squaglia,
When you create the reference, instead of typing in a formula, go to the cell that you want to reference, copy it, then go to the cell where you want the cell value and formatting, pastespecial formats, then pastespecial paste link. Of course, if the formatting of the original cell changes, the format of the linked cell won't be updated. HTH, Bernie MS Excel MVP "squaglia" wrote in message ... I am not sure my question is relevant to your discussion. I am new here. I want a font color to follow the numbers and or text when i reference that cell on another spreadsheet. How do i do that? "Bernie Deitrick" wrote: Kevin, Select cell A44, for example. Then from the main Excel menu, choose Format / Conditional Formatting.... and choose "Cell Value is.." and then one of the conditionals, and select your limit or condition. Then set the format that you want by clicking the format button. Look in help for examples. HTH, Bernie MS Excel MVP "kevinknight09" wrote in message news:kevinknight09.2bx7rf_1154535607.6597@excelfor um-nospam.com... Would you be willing to share the Conditional Formatting that you referenced? Again I apologize for my lack of Excel and VB knowledge. Thank you! -- kevinknight09 ------------------------------------------------------------------------ kevinknight09's Profile: http://www.excelforum.com/member.php...o&userid=37024 View this thread: http://www.excelforum.com/showthread...hreadid=567475 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing the color of a cell based on the color of another cell. | Excel Discussion (Misc queries) | |||
Changing the Color of Specific Characters using a Macro | Excel Worksheet Functions | |||
Macro for Changing Cell Background Color | Excel Programming | |||
Changing cell color | Excel Programming | |||
Excel VBA-Changing cell color with if then function | Excel Programming |