Forum Discussion
Help to modify if/then statement of an existing code
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).
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
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.
Ok, it's Monday, lol.
So, if I use either:
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=RC[-2],""True"", ""False"")"
or
ActiveCell.FormulaR1C1 = "=RC[-1]=RC[-2]"
with
If r.Value = False Then
...then the code works where when it finds False in the column and highlights the corresponding header, but also does the same thing for empty cells and 0. This might not be the end of the world, as there probably shouldn't be any empty cells in the range anyway....so finding one might be beneficial. But, when it finds a "0" could be a problem, but probably workable as this should never really happen. But, you never know.
However, using either formula with:
If r.Value = "False" Then
will ignore blanks and 0's and just highlight the columns with false!
and if i use:
If r.Formula = "False" Then
then nothing happens.
Thanks so much for your help!!
Greg
Here is the final code, if anyone searches for this and is interested. If the code finds "False" in any cell in the range, it will highlight the corresponding header red. The code determines the last column for you. You can change the color by changing the .ColorIndex number.
Sub redforheaders() 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.Value = "False" Then r2(1).Interior.ColorIndex = 3 Exit For End If Next r Next ic End SubGlad I could inspire you to figure it out yourself :))
Lol...probably because I'm a chemist, hacking my way though code to try to make my job easier. And only half-knowing what I'm doing. But, that's why I come here. You guys are great.
I'll give all these things a try on Monday.
Thanks!
Greg