Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to code macro for worksheet?
Does anyone have any suggesitons on how to code macro for worksheet?
Condition 1: In cell A1, there is a number 1234, under the range between cell B2 to Z50, if any cell matches the number 1234 in cell A1, then it's background color should change to red and it's text color should change to white. Condition 2: In cell A2, there is a number 2345, under the range between cell B2 to Z50, if any cell matches the number 2345 in cell A2, then it's background color should change to blue and it's text color should change to white. Condition 3: In cell A3, .... .... Condition 10: In cell A10, .... There is a priority for a list conditions, If condition 2,3,5,6,8,10 are false, condition 1,4,7,9 are true, only change the color for condition 1. If condition 1,2,3,5,6,8,10 are false, condition 4,7,9 are true, only change the color for condition 4. I cannot use Excel build-in function to change the background and text color, because it is limited to 3 different conditions only, but I get 10 conditions. Does anyone have any suggestions on how to code macro for worksheet to change the background and text color? Thanks in advance for any suggestions Eric |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to code macro for worksheet?
Hi,
Excel 2007 supports an unlimited number of conditions in the conditional format area. To code the colors you want for each condition turn on the recorder and change the background and font color of a single cell, working you way through all the combinations you want. Turn off the record. I am not clear whether there are 10 conditions or that is just the beginning, so your general macro will look something like this Dim ccell As Range Dim cell As Range Sub ColorCells() For Each ccell In Range("A1:A10") For Each cell In Range("B2:Z50") If ccell = cell Then Color End If Next cell Next ccell End Sub Sub Color() Select Case ccell.Row Case 1 cell.Interior.ColorIndex = 35 cell.Font.ColorIndex = 22 Case 2 End Select End Sub -- If this helps, please click the Yes button Cheers, Shane Devenshire "Eric" wrote: Does anyone have any suggesitons on how to code macro for worksheet? Condition 1: In cell A1, there is a number 1234, under the range between cell B2 to Z50, if any cell matches the number 1234 in cell A1, then it's background color should change to red and it's text color should change to white. Condition 2: In cell A2, there is a number 2345, under the range between cell B2 to Z50, if any cell matches the number 2345 in cell A2, then it's background color should change to blue and it's text color should change to white. Condition 3: In cell A3, .... ... Condition 10: In cell A10, .... There is a priority for a list conditions, If condition 2,3,5,6,8,10 are false, condition 1,4,7,9 are true, only change the color for condition 1. If condition 1,2,3,5,6,8,10 are false, condition 4,7,9 are true, only change the color for condition 4. I cannot use Excel build-in function to change the background and text color, because it is limited to 3 different conditions only, but I get 10 conditions. Does anyone have any suggestions on how to code macro for worksheet to change the background and text color? Thanks in advance for any suggestions Eric |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro code | Excel Discussion (Misc queries) | |||
Macro code | Excel Discussion (Misc queries) | |||
Deleting code from a macro (by a macro) | Excel Discussion (Misc queries) | |||
Macro code | Excel Discussion (Misc queries) | |||
Macro VB code help | Excel Discussion (Misc queries) |