I have a problem auto highlighting a spread sheet

[ShirinB]

Hi,

I am trying to auto highlight a spread sheet, my conditions are a bit specific and this doesnt let me to get the right outcome.
I tried many different kind of codes in <acronym title="vBulletin" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VB</acronym> but they did not work.

I need to highlight my rows if only three rows in one sequence are empty, and I want the first three columns not to be considered.

I dont want my loop to ask me for number of rows in spread sheet.

I dont know how...?

Is there any help?

Thanks

 

[saltkev]

Hi

If I understan correctly,

You wish columns "A-C" to be disregarded.
If any 3 Rows have zero value then highlight those rows.

Question.

Will the remainder of each row be completly empty or are we talking about a particular cell or range of cells.

regards

Kev

 

[ShirinB]

Hi Kev,

Thanks for your reply.

Actually, the last column which in my current spread sheet is column number 27, "AD", ican be disregarded as well, so the first three columns and the last one column in a row must be ignored.

And yes, if there are three rows with the above condition and they have zero values, then they get highlighted.

Is that possible?


Regards,
Shirin

 

[saltkev]

Hi

Try this on a copy of your sheet. Put it under a command button. Read Through the code first to see if you need to make alterations.

Sheets("sheet1").Activate ' Adjust to suit
Application.ScreenUpdating = False
Static fx, fx1, fx2, Lr, LRange, holder, holder1, holderrow
' ##########  Setup Working Ranges ################
Lr = Sheets("sheet1").Range("d1000000").End(xlUp).Row
      
LRange = Sheets("sheet1").Range("d2:d" & Lr).Address(False, False) ' Adjust to Suit Your Range

' ########## Loop Through Each Cell to Define Content ##############
For Each fx In ActiveSheet.Range(LRange)
holder = fx.Address(0, 0) & ":" & fx.Offset(0, 25).Address(0, 0)
        For Each fx1 In ActiveSheet.Range(holder)
 
            holder1 = 0
            holderrow = ""
                If fx1 = "" Then
                    'Do Nothing
                Else
 
                    holder1 = holder1 + 1
                    holderrow = "AE" & fx1.Row
                        If holder1 > "" Then
                            ActiveSheet.Range(holderrow).Value = 1
                        End If
                End If
 
        Next fx1
Next fx
'################# Select & Highlight Rows ################
For Each fx2 In ActiveSheet.Range(LRange)

        If fx2.Offset(0, 27).Value = "" And fx2.Offset(-1, 27).Value = "" And fx2.Offset(1, 27).Value = "" Then
        ActiveSheet.Range(fx2.Offset(-1, 0).Address(0, 0) & ":" & fx2.Offset(1, 0).Address(0, 0)).EntireRow.Select
        
                With Selection.Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .Color = 65535
                        .TintAndShade = 0
                        .PatternTintAndShade = 0
                End With
                      
        End If
Next fx2

Columns("AE:AE").ClearContents

regards

Kev

 

[ShirinB]

Hi Kev,

Thank you so much. It works great.
But there is a minor problem which I dont know where is its source in the code,
The code highlights the first few rows and the last few rows and they are not empty. I tried to attach a screen shot but seems I can not attach an image to the forum.
I think it might be because it is because it does not consider the first few rows and the last rows as "Working Range"


Regards,

 

[pjmorris]

do the Zeros have to be in any particular position? If not then you could use conditional formatting. It requires a little care to setup, but would work without the need for a macro.

The basis of operation is this formula =AND(COUNTIF($D1:$AC1,0)>0,COUNTIF($D2:$AC2,0)>0,COUNTIF($D3:$AC3,0)>0)

Do the following:

1. Select the entire area of your data, then from Home ribbon 'Conditional Formating', 'New Rule', 'Use a Formula to determine which cells to format', then in the box marked 'Format Cells where this value is true' enter the formula above. Set the format to whatever you choose.

2. Select the entire area of your data, but excluding the first row, then from Home ribbon 'Conditional Formating', 'New Rule', 'Use a Formula to determine which cells to format', then in the box marked 'Format Cells where this value is true' enter the formula above. Set the format to whatever you choose.

3. Select the entire area of your data, but excluding the first and second rows, then from Home ribbon 'Conditional Formating', 'New Rule', 'Use a Formula to determine which cells to format', then in the box marked 'Format Cells where this value is true' enter the formula above. Set the format to whatever you choose.

 

[saltkev]

Hi

Send me a copy of your sheet containg dummy data & I will have a look.

kevin.eames@airbus.com

regards

Kev