• 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...

  • 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?

Eloise T

Active Member
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:
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
 

Attachments

  • Chandoo - PAY STUB CHECKER - REDnBOLD fix.xlsm
    50.2 KB · Views: 18
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:
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
 
Hi !
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.
I opened workbook under my Excel 2003 version updated with the
Microsoft Office Compatibility Pack File Formats …
 
Hi !

I opened workbook under my Excel 2003 version updated with the
Microsoft Office Compatibility Pack File Formats …
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.
 
ps: the code should be

View attachment 45360
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?
 
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.
 
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?
 
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
 

Attachments

  • Book2.xlsm
    17.2 KB · Views: 5
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
 
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
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
 
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:
'  .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]
 
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
 
Back
Top