Adjacent Cell Conditional Format Problem Closed

Question

Hello, 

I have a large-ish sheet (14000 rows) where I need to quickly validate whether or not values are missing based on adjacent cells.    Each row has multiple "two part" data pieces that need to be validated.  

* I need to validate if "Value" cell data is missing ("BLANK" just to illustrate below) based on whether there IS data (nonblank) in the "Variable" column to it's left.  *



For example 
  
      A          B         C        D         E         F         G              
1  Address |  Variable | Value | Variable | Value | Variable | Value |
2  123ABC        A         1        B         4         C        7         
3  ABC123        A         1        B     (BLANK)                
4  12CBA3        A         1        B         4         C     (BLANK)  
5  32BCA1        A         3        B         4
6  23CAB1        A         1        B         6         C        7          
.    etc.
.    etc.
.


Just to clarify:
In the case were there is nothing in the variable column (i.e. F5), then I don't need to worry about the adjacent cell (G5) being blank,  just the ones where there IS data in the Variable column, but no data in the Value column to the right.  


I was thinking I could write a complex CF formula and apply it to the whole sheet, but saw some other posts that got me thinking maybe this would require some VBA. Not sure.

Ideally, I would like to ID the cells w/ that are missing with a Text String like "BLANK" so that I can count row and sheet totals.


Any help much appreciated and have a great weekend!




Configuration: Windows 7 / Safari 532.5

rizvisa1 · Answer

try this.  It will put (Blank) if starting from column 2, if the even column (2,4,6...) is not blank and next odd column (3, 5, 7) is blank


Sub FillInCell()
Dim iMaxCols As Integer
Dim iCol As Integer
Dim lMaxRows  As Long

    ActiveSheet.AutoFilterMode = False

    iMaxCols = Cells(1, Columns.Count).End(xlToLeft).Column
    
    Cells.Select
    Selection.AutoFilter
    
    
    For iCol = 2 To iMaxCols Step 2
    
        Selection.AutoFilter Field:=iCol, Criteria1:="<>"
        Selection.AutoFilter Field:=iCol + 1, Criteria1:=""
    
        lMaxRows = Cells(Rows.Count, iCol).End(xlUp).Row
        
        If lMaxRows > 1 Then
        
            Range(Cells(2, iCol + 1), Cells(lMaxRows, iCol + 1)).Value = "(Blank)"
            
        End If
        
        Selection.AutoFilter
    Next iCol
    
End Sub

Adjacent Cell Conditional Format Problem

1 response

Similar discussions

Subscribe To Our Newsletter!