Forum Discussion
GMSchaaf
Nov 08, 2019Copper Contributor
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.
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
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_EekelenPlatinum Contributor
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).
- GMSchaafCopper Contributor
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_EekelenPlatinum Contributor
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.