Colors overridden in a Macro

[Sharon]

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

[Norman Jones]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[Norman Jones]

Hi Sharon,

> =$B1="B"

Should be

=$B1="C"

where C is the value that is to trigger the conditional format.

---
Regards,
Norman



"Norman Jones" <[email protected]> wrote in message
news:%237sg0%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>
>