Forum Discussion

GMSchaaf's avatar
GMSchaaf
Copper Contributor
Nov 08, 2019
Solved

Help to modify if/then statement of an existing code

Hi, 
 

I found this bit of code that works very well to go through a spreadsheet and when it finds a colored cell, it colors the corresponding header red. Works great.

 

https://stackoverflow.com/questions/35975076/finding-all-cells-that-have-been-filled-with-any-color-and-highlighting-correspo

 

I'd like to modify it though so that instead of looking for a color, it looks for the word "false" and then highlights the column header.

 

I've tried alot of things, but can't get the syntax correct. 

 

Any ideas?

 

Thanks,

 

Greg

 

 

 

 

 

 

 

Sub nexttry()

    Dim r1 As Range, r2 As Range, r As Range
    Dim nFirstColumn As Long, nLastColumn As Long, ic As Long

    Set r1 = ActiveSheet.UsedRange
    nLastColumn = r1.Columns.Count + r1.column - 1
    nFirstColumn = r1.column

    For ic = nFirstColumn To nLastColumn
        Set r2 = Intersect(r1, Columns(ic))
        For Each r In r2
            If r.Interior.ColorIndex <> xlNone Then
' Need new if/then statement here, something like:
'           If r = "False" then
                r2(1).Interior.ColorIndex = 3
                Exit For
            End If
        Next r
    Next ic

End Sub

 

 

 

 

 

  • GMSchaaf 

     r.Formula = "False" Then will do the trick if you want to look for the text "False". If you want to change the header when a cell contains the value FALSE (like in TRUE or FALSE) you can use  r.Value = False Then. But this one will also color the header when it finds blank cells and cells that contain 0 (zero).

     

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor

    GMSchaaf 

     r.Formula = "False" Then will do the trick if you want to look for the text "False". If you want to change the header when a cell contains the value FALSE (like in TRUE or FALSE) you can use  r.Value = False Then. But this one will also color the header when it finds blank cells and cells that contain 0 (zero).

     

    • GMSchaaf's avatar
      GMSchaaf
      Copper Contributor

      Riny_van_Eekelen 

       

      hmmmm...

       

      The "False" would result from a formula, =if(A1=B1,"True", False") and then that is autofilled to the last row....and this is done for many columns in the spreadsheet. Then, conditional formatting applied to make all the "false" cells show up red. But, its a long spreadsheet, so I have to scroll down to look for red, or sort by red, or filter by red. Having the column header show up red would be a nice little trick to save time and frustration. 

       

      (apparently modifying this code to look for the color that it put out by conditional formatting is....difficult. At least form all the searches I did. I thought changing it to look for "false" would be easy)

       

      I'll try it when I get back to the office Monday. 

       

      I guess I could have the code cut/paste values so there's not a formula there anymore. Because, yeah, there's alot of cells for the code (as its written) to look though and could find a 0 or blank cell. At least there are some options.

       

      Thanks,

       

      Greg

      • Riny_van_Eekelen's avatar
        Riny_van_Eekelen
        Platinum Contributor

        GMSchaaf 

         

        Just wondering why you use IF. Use =A1=B1 in stead. That will result in a "real" value of TRUE or FALSE which can be tested by using "r.Value". If you copy it all the way down you should not get blanks or zeros. See if it works, otherwise let me know on Monday.

         

Resources