VBA strikethrough of cells

  • I have been researching VBA to strike through text in cells when criteria is met.
    I was trying to use a ref cell that is a REF number and Strikethrough text in Cells 1-8 if the ref number is below the ref # and Greater than a specific date.


    The Ref # determines where you start and everything greater than DATE wise thats also provided on sheet1 My desire is to strikethrough text in cells A:M (1-13)
    I've researched and am unable to ID strike through.

  • No VBA no matter how well written can be as efficient as an inbuilt Excel function. One downside as well is that you need to be sure users enable macros unlike using Conditional Formatting.


    Basically, why re-invent the wheel?

  • Its definitely multiple rows. I have about 10K of data and was looking to strike-through the rows that met the criteria. I also have attempted to work a formula but have struggled with how to use the conditional format for this project. Thanks and a appreciate the help.

  • Test on backup copy of sheet2. Click the tab, View Code, and paste this. Then select A2 down, cut, paste, to make it run for each row.


    I read your post #1 to strikethrough if column A is > sheet1 ref and column M is > sheet1 date.


  • Thanks and appreciate the help.Sorry for my delay airborne for a little while. I ran the code and honestly didn't understand the cut and paste but figured it out. The code currently strike-throughs all rows below the REF C5 and in many rows have less than the date in E5. I think i said it incorrectly date in column "M" should be less than. Additionally, it only strike throughs column A and J:M unsure why just B:J does not strike through. Thanks again I will continue to work.

  • When I make the macro run for all the rows, row 6 and down get strikethrough just like your results show on sheet3.


    I can not replicate your partial column strikethrough issue.


    It is very easy to change the equality check. Change > to >= or < or <=. Change AND to OR as needed.

    Code
    1. If Cells(c.Row, "A") [COLOR=#FF0000]>[/COLOR] ref [COLOR=#FF0000]And[/COLOR] Cells(c.Row, "M") [COLOR=#FF0000]>[/COLOR] d Then


    Once it works as needed, you may want to add some other triggers to make it even more automatic. Right now, it only triggers if column A or M values are changed. e.g. Add another change event on Sheet1 for the ref and d value cells.

  • Kenneth,


    I appreciate the assistance. I was looking at the code and it works as i requested. I've realized that i need to add another variable. Running found a bigger flaw in my DATA and THANK YOU. I need to run this macro only if the criteria in column "C". I put the REF on shee1 If column "C" on sheet2 matches. In my example column C matches then applies to Macro only on the rows thats meets. I apologize for any confusion but couldnt figure out why my results didnt make sense. The funny thing it was doing exactly what i requested. Thanks again. I attached an example of the new requirement.

  • I am confused. I think you now just want the one condition? If you can make a formula give the expected results, that can explain what you need.


    So, in Sheet2!N2, put this formula in and drag down. =IF(A2<Sheet1!$C$5,"No StrikeThrough","StrikeThrough")
    Normally, one wants a True or False logic to apply a format or not. I wrote this to make it clear what you wanted if the condition was true, no strikethrough.


    In conditional formats we want a True result to apply a format. The False result would mean no format change. In that case, we would reverse the equality sign or reverse the order of both sides. So, this equation could be put into O2 and fill down to test. =IF(A2>Sheet1!$C$5,TRUE,FALSE)


    In that 2nd formula, we can interpret a True result to mean True, do not strikethrough. It can get confusing if you don't keep your intent consistent. That is why I was explicit in the results to return in the first =If().


    Once I understand your condition(s), I can show a code example or conditional format method.

  • I created the confusion thats on me not understanding what i was requesting. The current VBA does exactly how i requested I just need to add only to the rows that has TEST1 in column "C". Currently I would like to add


    '=if(Sheet2!C2:C)=Sheet1!G5 then perform the macro as you created. With current code I strikethrough rows that have test2 in column "C" which is not desired.. I import data and paste to sheet2 this data will always be in order lowest to highest and Column "C" group all the TEST1, TEST2 together. So i desire to look in Column "C" and if criteria is = sheet1G5 only apply the strikethough to rows that equal to Sheet1G5. Again thanks and appreciate your help the CODE works great but i misunderstood my data. I added a better example

  • Let's simplify this. Is it just the one case as in post #12 or is it plus the other two cases? If the latter:


    If the former:

    Code
    1. 'If Cells(c.Row, "A") > ref And Cells(c.Row, "M") > d Then
    2. If Cells(c.Row, "C") = t Then
  • If you want to do it via Conditional Formatting in 30 seconds:


    1. Select the range of cells A2 and down and over to your last column's cell.
    2. Select Conditional Formatting on your Home ribbon, New Rule, select the last option - Use a Formula...
    3. The formula is: =Sheet1!$G$5<>$C2.
    a. Note that the 2 does not have a prefix of $. This means that row 2 is relative, not absolute.
    4. Click the Format button on that dialog.
    5. Check the box to Strikethrough.
    6. OK, OK or Ok, Apply, Ok.


    Using this method, anytime G5 on Sheet1 is changed or C2 and down is changed, the font format of strikethrough is set or unset.


    Using the worksheet code method, to make it the change if G5 on sheet1 changes, worksheet code for that sheet is needed.

  • Thanks the biggest thing I am trying to learn on this code was i have never done

    Code
    1. Private Sub Worksheet_Change(ByVal Target As Range)


    I have made numerous workbooks with buttons everywhere assigned a macro. I understand what a Private Sub does but is it more efficient? looking up and studying and trying to make sure i understand the difference. Thanks for sharing your knowledge its definitely appreciated.

  • The key to understand is that it is an Event. In this case, the worksheet's Change event triggers automatically. Sometimes you want a more global trigger. In those cases, use the events in ThisWorkbook object. e.g. Do some action when the Workbook Opens. Or, use the Change event in it to trigger for all worksheets for some event like maybe Change.

  • The code works perfect but how do i modify from an event driven trigger to a button and assign a Macro. I am finding that with current use I have to create a trigger. Again the code works great but struggling on transitioning from event trigger. Thanks and wasnt sure if i was suppose to create a new post or add on existing