Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.programming
|
|||
|
|||
How to code macro for worksheet?
This worked for me with 3 conditions
Sub colors() Dim colour() As Integer Dim nCond As Integer 'store the number of conditions in nCond nCond = 3 ReDim colour(nCond) colour(1) = 3 colour(2) = 7 colour(3) = 12 'Repeat for the number of conditions 'Loop for the number of conditions For i = 1 To nCond For j = 1 To 50 For k = 2 To 26 'the conditions with the least importqant first and the most important as last. If Cells(j, k) = Cells(nCond + 1 - i, 1) Then Cells(j, k).Select 'change the font into white Selection.Font.ColorIndex = 2 With Selection.Interior 'change the background color .ColorIndex = colour(i) .Pattern = xlSolid .PatternColorIndex = xlAutomatic End With End If Next k Next j Next i End Sub "Eric" schreef in bericht ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to code macro for worksheet?
Sorry
The backgroundcolor should be: .ColorIndex = colour(nCond + 1 -i) "VadeRetro" schreef in bericht ... This worked for me with 3 conditions Sub colors() Dim colour() As Integer Dim nCond As Integer 'store the number of conditions in nCond nCond = 3 ReDim colour(nCond) colour(1) = 3 colour(2) = 7 colour(3) = 12 'Repeat for the number of conditions 'Loop for the number of conditions For i = 1 To nCond For j = 1 To 50 For k = 2 To 26 'the conditions with the least importqant first and the most important as last. If Cells(j, k) = Cells(nCond + 1 - i, 1) Then Cells(j, k).Select 'change the font into white Selection.Font.ColorIndex = 2 With Selection.Interior 'change the background color .ColorIndex = colour(i) .Pattern = xlSolid .PatternColorIndex = xlAutomatic End With End If Next k Next j Next i End Sub "Eric" schreef in bericht ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to code macro for worksheet?
Thank you very much for suggestions
Will it be possible to those code into each specific worksheet? so it will automatically run this macro without manually updated it? Thank you very much for any suggestions Eric "VadeRetro" wrote: Sorry The backgroundcolor should be: .ColorIndex = colour(nCond + 1 -i) "VadeRetro" schreef in bericht ... This worked for me with 3 conditions Sub colors() Dim colour() As Integer Dim nCond As Integer 'store the number of conditions in nCond nCond = 3 ReDim colour(nCond) colour(1) = 3 colour(2) = 7 colour(3) = 12 'Repeat for the number of conditions 'Loop for the number of conditions For i = 1 To nCond For j = 1 To 50 For k = 2 To 26 'the conditions with the least importqant first and the most important as last. If Cells(j, k) = Cells(nCond + 1 - i, 1) Then Cells(j, k).Select 'change the font into white Selection.Font.ColorIndex = 2 With Selection.Interior 'change the background color .ColorIndex = colour(i) .Pattern = xlSolid .PatternColorIndex = xlAutomatic End With End If Next k Next j Next i End Sub "Eric" schreef in bericht ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to code macro for worksheet?
Sub colors()
Dim colour() As Integer Dim nCond As Integer 'store the number of conditions in nCond For a = 1 To Sheets.Count Sheets(a).Activate nCond = 3 ReDim colour(nCond) colour(1) = 3 colour(2) = 7 colour(3) = 12 'Repeat for the number of conditions 'Loop for the number of conditions For i = 1 To nCond For j = 1 To 50 For k = 2 To 26 'the conditions with the least importqant first and the most important as last. If Cells(j, k) = Cells(nCond + 1 - i, 1) Then Range(Cells(j, 1), Cells(j, 26)).Select 'change the font into white Selection.Font.ColorIndex = 2 With Selection.Interior 'change the background color .ColorIndex = colour(nCond + 1 - i) .Pattern = xlSolid .PatternColorIndex = xlAutomatic End With End If Next k Next j Next i Next a End Sub "Eric" schreef in bericht ... Thank you very much for suggestions Will it be possible to those code into each specific worksheet? so it will automatically run this macro without manually updated it? Thank you very much for any suggestions Eric |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to code macro for worksheet? | Excel Worksheet Functions | |||
Adding macro code to a new worksheet | Excel Programming | |||
macro to alter worksheet code | Excel Programming | |||
Delete VBA code in worksheet through a macro | Excel Programming | |||
Close worksheet with Macro or Code | Excel Programming |