Ark68
Well-known Member
- Joined
- Mar 23, 2004
- Messages
- 4,564
- Office Version
- 365
- 2016
- Platform
- 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.
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
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