Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Colors overridden in a Macro
Hello all,
I'm a newbie with excel, and would like to ask a question: I was looking for a technique to paint entire rows based on the value of a cell in that row, and found in this group the following script: 'In case cell B equals "C", paint the cell with color index 24 Sub Color_rows() Dim FirstAddress As String Dim myArr As Variant Dim rng As Range Dim I As Long Application.ScreenUpdating = False myArr = Array("C") 'You can also use more values in the Array Cells.Interior.ColorIndex = xlNone 'set the fill color to "no fill" in all cells With Range("B:B") For I = LBound(myArr) To UBound(myArr) Set rng = .Find(What:=myArr(I), After:=Range("B" & Rows.Count), LookAt:=xlWhole) 'If you want to search in a part of the rng.value then use xlPart If Not rng Is Nothing Then FirstAddress = rng.Address Do rng.EntireRow.Interior.ColorIndex = 24 'make the row red Set rng = .FindNext(rng) Loop While Not rng Is Nothing And rng.Address < FirstAddress End If Next I End With Application.ScreenUpdating = True End Sub Now comes my question: The above script overrides the colors of previously formatted cells, in which the B cell doesn't contain the required condition (that is, does not equal "C"). So, what it actually does is, it paints the rows in which B="C", and UNpaints those who don't have the condition. Also, when a row has the above mentioned B="C", not always the script overrides the colors of a previously formatted cell, so I don't really understand what is going on. The rationalle of what I'm doing is, that I need to distinguish between rows that contain ongoing data and rows that contain data that was already finalized, and therefore doesn't need to have its original colors, but to be painted in its entirety instead. On the other hand, the above mentioned ongoing data rows, shouldn't change their colors as a result of running the macro. Whoever got this far, Thanks a lot ! Sharon |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Colors overridden in a Macro
Hi Sharon,
Instead of using your macro, consider using conditional formatting. Select the entire range (potentially) to be colored and set the condtional 'Formula Is' value to: =$B1="B" This will color appropriate rows (up to the last columm you selected for CF), but will not destroy any existing fill color arrangement. If you want to do this with VBA, turn on the macro recorder while you perform the manual operations and then edit the resultant code. If you use VBA, you would need some means enabling your procedure to distinguish 'ongoing data' rows from 'finalized' data rows. --- Regards, Norman "Sharon" wrote in message ups.com... Hello all, I'm a newbie with excel, and would like to ask a question: I was looking for a technique to paint entire rows based on the value of a cell in that row, and found in this group the following script: 'In case cell B equals "C", paint the cell with color index 24 Sub Color_rows() Dim FirstAddress As String Dim myArr As Variant Dim rng As Range Dim I As Long Application.ScreenUpdating = False myArr = Array("C") 'You can also use more values in the Array Cells.Interior.ColorIndex = xlNone 'set the fill color to "no fill" in all cells With Range("B:B") For I = LBound(myArr) To UBound(myArr) Set rng = .Find(What:=myArr(I), After:=Range("B" & Rows.Count), LookAt:=xlWhole) 'If you want to search in a part of the rng.value then use xlPart If Not rng Is Nothing Then FirstAddress = rng.Address Do rng.EntireRow.Interior.ColorIndex = 24 'make the row red Set rng = .FindNext(rng) Loop While Not rng Is Nothing And rng.Address < FirstAddress End If Next I End With Application.ScreenUpdating = True End Sub Now comes my question: The above script overrides the colors of previously formatted cells, in which the B cell doesn't contain the required condition (that is, does not equal "C"). So, what it actually does is, it paints the rows in which B="C", and UNpaints those who don't have the condition. Also, when a row has the above mentioned B="C", not always the script overrides the colors of a previously formatted cell, so I don't really understand what is going on. The rationalle of what I'm doing is, that I need to distinguish between rows that contain ongoing data and rows that contain data that was already finalized, and therefore doesn't need to have its original colors, but to be painted in its entirety instead. On the other hand, the above mentioned ongoing data rows, shouldn't change their colors as a result of running the macro. Whoever got this far, Thanks a lot ! Sharon |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Colors overridden in a Macro
Hi Sharon,
=$B1="B" Should be =$B1="C" where C is the value that is to trigger the conditional format. --- Regards, Norman "Norman Jones" wrote in message ... Hi Sharon, Instead of using your macro, consider using conditional formatting. Select the entire range (potentially) to be colored and set the condtional 'Formula Is' value to: =$B1="B" This will color appropriate rows (up to the last columm you selected for CF), but will not destroy any existing fill color arrangement. If you want to do this with VBA, turn on the macro recorder while you perform the manual operations and then edit the resultant code. If you use VBA, you would need some means enabling your procedure to distinguish 'ongoing data' rows from 'finalized' data rows. --- Regards, Norman "Sharon" wrote in message ups.com... Hello all, I'm a newbie with excel, and would like to ask a question: I was looking for a technique to paint entire rows based on the value of a cell in that row, and found in this group the following script: 'In case cell B equals "C", paint the cell with color index 24 Sub Color_rows() Dim FirstAddress As String Dim myArr As Variant Dim rng As Range Dim I As Long Application.ScreenUpdating = False myArr = Array("C") 'You can also use more values in the Array Cells.Interior.ColorIndex = xlNone 'set the fill color to "no fill" in all cells With Range("B:B") For I = LBound(myArr) To UBound(myArr) Set rng = .Find(What:=myArr(I), After:=Range("B" & Rows.Count), LookAt:=xlWhole) 'If you want to search in a part of the rng.value then use xlPart If Not rng Is Nothing Then FirstAddress = rng.Address Do rng.EntireRow.Interior.ColorIndex = 24 'make the row red Set rng = .FindNext(rng) Loop While Not rng Is Nothing And rng.Address < FirstAddress End If Next I End With Application.ScreenUpdating = True End Sub Now comes my question: The above script overrides the colors of previously formatted cells, in which the B cell doesn't contain the required condition (that is, does not equal "C"). So, what it actually does is, it paints the rows in which B="C", and UNpaints those who don't have the condition. Also, when a row has the above mentioned B="C", not always the script overrides the colors of a previously formatted cell, so I don't really understand what is going on. The rationalle of what I'm doing is, that I need to distinguish between rows that contain ongoing data and rows that contain data that was already finalized, and therefore doesn't need to have its original colors, but to be painted in its entirety instead. On the other hand, the above mentioned ongoing data rows, shouldn't change their colors as a result of running the macro. Whoever got this far, Thanks a lot ! Sharon |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Used drawing colors in shapes....lost default colors for "Fill Col | Excel Discussion (Misc queries) | |||
What do I need to add to my macro to make the cell colors change | Excel Discussion (Misc queries) | |||
Worksheet formatting (fill colors & text colors) disappeared | Excel Discussion (Misc queries) | |||
Lost highlighting and font colors; background colors on web pages | Excel Discussion (Misc queries) | |||
macro used to change colors | Excel Worksheet Functions |