1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to add an extra parameter to VBA?

Discussion in 'VBA Macros' started by Eloise T, Sep 9, 2017.

  1. Eloise T

    Eloise T Active Member

    Messages:
    656
    Cell G13 should have the "70" of the data in bold red font like the 6 cells above it.
    The macro REDnBOLD is responsible for accomplishing that, but it doesn't comply without a "prefix" in front of the "70". How do I tweak the macro so the "70" will be 70?


    upload_2017-9-9_9-29-56.png

    Code (vb):

    Sub REDnBOLD()
    ' This VBA segment changes the TV Model screen size between 70 and 90 inches to red and Bold and starts in Row 5, Column C.
     For Each cll In Range(Cells(6, "G"), Cells(Rows.Count, "G").End(xlUp)).Cells
          With cll
              x = Evaluate("MIN(IFERROR(FIND(ROW(10:99)," & .Address(0, 0, , 1) & "),""""))")
              If x > 0 Then
              y = CLng(Mid(cll.Value, x, 2))
                  If y >= 70 And y <= 90 Then
                      With .Characters(Start:=x, Length:=2).Font
                          .FontStyle = "Bold"
                          .Color = -16776961  'Red
                     End With
                  End If
              End If
          End With
      Next cll
    End Sub
     

    Attached Files:

  2. p45cal

    p45cal Well-Known Member

    Messages:
    924
    I can't check as I only have access to an Excel 2003 machine right now, but I very strongly suspect that you have conditional formatting overriding the vba applied formatting.
    If you remove the conditional formatting from that cell (and you have run the macro at least once beforehand) you should see the red bold appear without having to run the macro again.
    Last edited: Sep 10, 2017
  3. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,836
    Eloise T

    The macro is working fine

    It is the Red CF that is resetting the Font to Black and No Bot Bold or Red

    Select G7:G16
    Goto the CF menu
    Manage Rules
    Select the Red CF
    Edit Rule
    Select Format
    Clear
    Apply

    upload_2017-9-10_15-5-17.png
  4. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,836
    ps: the code should be

    upload_2017-9-10_15-6-20.png
  5. Marc L

    Marc L Excel Ninja

    Messages:
    3,277
    Hi !
    I opened workbook under my Excel 2003 version updated with the
    Microsoft Office Compatibility Pack File Formats …
  6. p45cal

    p45cal Well-Known Member

    Messages:
    924
    Yes, but when you open it that way, you're told that some cells have more conditional formats than are allowed in Exel 2003 and you'll only have 3 of them. What's more the macro uses Evaluate wirh IFERROR and this isn't available to Excel 2003
    Rather than complete removal the offending conditional formats, I feel there might be a way to tweak the conditional formatting so it doesn't try and alter fonts or their colours but it wasn't worth me experimenting with Excel 2003 as there are significant changes in this area in subsequent versions of Excel.
    NARAYANK991 likes this.
  7. Eloise T

    Eloise T Active Member

    Messages:
    656
    Thanks for the "7" fix! :)

    I preformed your instructions:
    Select G7:G16
    Goto the CF menu
    Manage Rules
    Select the Red CF
    Edit Rule
    Select Format
    Clear
    Apply

    and got:
    upload_2017-9-10_14-50-24.png
    For whatever reason the gradient-shading got "lost."
    What you can't see is the "70" is bold and red.

    Sooooo, I went into the Conditional_Format_Reset macro
    just below the '70" TVs ---------------------------- line...

    and commented out the "Black font" line:
    ' .FormatConditions(.FormatConditions.Count).Font.ColorIndex = 1 'Black font

    and got:
    upload_2017-9-10_14-59-57.png
    Notice the bottom two 70s are not both RED and Bold like they are intended.
    So again, I went into the Conditional_Format_Reset macro
    just below the '70" TVs ---------------------------- line...
    and made Bold = True

    .FormatConditions(.FormatConditions.Count).Font.Bold = True

    ...and got:
    upload_2017-9-10_15-9-17.png
    How can this be remedied? There's no problem when there's a
    Alpha character in front of the "70" but without the Alpha, it
    messes up. Is there a tweak for the macro REDnBOLD?
  8. p45cal

    p45cal Well-Known Member

    Messages:
    924
    It is the conditional formatting doing this, not the macro.
    Examine the one which includes SearchFor70, make sure it neither changes the font style of the whole cell, nor colours the font. I can't explain how to do it because I don't have the appropriate version of Excel. There might be a clear button in the CF dialogue, I don't know. Also make sure your CF re-instatement procedure doesn't run and replace manual work you've done.
    To satisfy yourself that it really is the CF overriding the macro RednBold, having once run that macro, remove all CF from G13 alone. The bold and red will reappear.
  9. Eloise T

    Eloise T Active Member

    Messages:
    656
    OK... The contents of G13 "70UH" and the contents of G7 "D70" both appear in the same Named Range "SearchFor70." Why are they not treated the same by the RednBold macro/Conditional Format as shown in Permalink (message) #7 above?
  10. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,924
    Hi ,

    I am attaching a file , which is self-explanatory.

    Go through it , and implement all the other conditions using the same code , and everything should work correctly.

    Narayan

    Attached Files:

  11. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,836
    The CF are incorrect
    If you select J7:J17
    the CF's refer to Column G
    they should refer to Column J

    Then add CF's for other Sizes
  12. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,924
    Hi Hui ,

    I think I should clarify.

    Each of the ranges G7:G16 , H7:H16 , I7:I16 and J7:J16 has formatting which has resulted from running the procedures(s) mentioned in G3:J4.

    Thus , the formatting in G7:G16 is the result of running only the RED n BOLD procedure on the range. When running this procedure , all references within the code were solely to this range.

    The formatting in H7:H16 is the result of running only the CF Reset procedure on the range. When running this procedure , all references within the code were solely to this range.

    The rule itself should not matter because the data is identical in all 4 ranges.

    Narayan
  13. Eloise T

    Eloise T Active Member

    Messages:
    656
    Thank you both, Narayan and Hui. for your help.

    I'm disappointedly amazed that, for the most part, commenting out 3 lines that, from my perspective, shouldn't have mattered, caused it to work properly, namely:

    Code (vb):

    '  .FormatConditions(.FormatConditions.Count).Font.ColorIndex = 1  'Black font
    '  .FormatConditions(.FormatConditions.Count).Font.Bold = False   ' Bold
    '  .FormatConditions(.FormatConditions.Count).Font.Italic = False
    especially when it wasn't necessary to do it for the other 5 segments, i.e.
    75, 80, 84, 85 and 90!



    On the matter of the other part of the "fix," what is the difference between:

    1) For Each cll In Range(Cells(7, "G"), Cells(Rows.Count, "G").End(xlUp)).Cells

    ...and its replacement in the REDnBOLD macro...

    2) With ws.Range("G7:G257") ?

    I understand the 2nd line pinpoints where the action will take place ("G7:G257"), but doesn't line 1 essentially do the same thing with less precision?

    ...."For each cell in Range beginning at Row 7 in Column G
    [For Each cll In Range(Cells(7, "G"),]

    and continuing for the entire Column G
    [Cells(Rows.Count,]

    until you run out of data, correct?
    ["G").End(xlUp)).Cells]
  14. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,924
    Hi ,

    There is no contradiction , actually.

    If you manually format a cell with any kind of format , and then apply a CF rule to the same cell , you can see that the conditional formatting will override the manual format.

    What the RED n BOLD procedure is doing is applying a format through code , which is equivalent to a user manually formatting a range ; the procedure Conditional_Format_Reset is applying CF rules through code , and it will override the formats applied by the RED n BOLD procedure where ever the two are not mutually exclusive.

    As for your other doubt , the expression :

    Range(Cells(7, "G"), Cells(Rows.Count, "G").End(xlUp)).Cells

    is arriving at a range , in a dynamic fashion ; what ever be the extent of data , this expression will find the correct last row , and use the resulting range as the Applies To range ; Rows.Count is the last row of any Excel worksheet in any version ; applying the End(xlUp) method to it results in a row which is the last row of data in the specified column , column G in this case.

    Thus , if the data extends till row 543 , the result of the above expression will be the same as using Range("G7:G543").

    Hence this usage is dynamic , whereas the usage Range("G7:G257") is static , and will not change if you add more data to your worksheet.

    Narayan

Share This Page