What is this code doing, and is it doing what I think it should be?

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
4,564
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I have this code, which I'm not entirely sure what it does, let alone how it does it. Someone kindly contributed as a solution a long time ago when I was last working on it. I forget now.

Code:
                    cntcls = Application.WorksheetFunction.CountA(.Range("H" & rw & ":Q" & rw))
                    If cntcls > 0 Then
                        With .Range("H" & rw & ":Q" & rw) 'Rows(dr_trow)
                            On Error Resume Next
                            Set c = .SpecialCells(xlConstants)
                            If c Is Nothing Then MsgBox "no cells", vbCritical: Stop
                            On Error GoTo 0
                            'If c.Value <> "AUTO" Then  '<-this doesn't work
                            For Each cl In c.Cells                                'loop through this cells
                                If cl.Column = 1 Then
                                    bLeft = False
                                Else
                                    bLeft = (cl.Offset(, -1).Interior.ColorIndex = xlNone And Len(cl.Offset(, -1).Value) = 0)
                                bLeft = (cl.Offset(, -1).Interior.ColorIndex = 2) 'And Len(cl.Offset(, -1).Value) = 0)
                                End If
                                If cl.Column = ActiveSheet.Columns.Count Then
                                    bRight = False
                                Else
                                    bRight = (cl.Offset(, 1).Interior.Color = ColorIndex = xlNone And Len(cl.Offset(, 1).Value) = 0)
                                End If
                                If Not bLeft And Not bRight Then cl.ClearContents
                            Next
                        End With

I think what it's supposed to do in part, and it's not, is to assess cells of row 'rw' in columns H through Q and change the cell shading (?) of that cell under a certain condition.
What it's doing, is taking any cell in that range, that has a value in it and clearing the contents. So, the end result is an empty unshaded cell. I suspect what it's supposed to do is keep the value (under some condition) and shade the cell grey? In some cells before the code runs, the text values in them are coloured white (to hide them), while others are visible. This code gets rid of all values when it reaches the cells. I don't want the values in the visible cells to disappear, but just to be givenm a grey shade. Perhaps this is not what this code is meant to do
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Forum statistics

Threads
1,214,907
Messages
6,122,183
Members
449,071
Latest member
cdnMech

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top