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.

 

[Norman Jones]

Hi Sharon,

=$B1="B"

Should be

=$B1="C"

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