+ Reply to Thread
Results 1 to 19 of 19

If text is of specific formt change another column colour to grey and add text in another

  1. #1
    Registered User
    Join Date
    01-22-2014
    Location
    Pretoria, South Africa
    MS-Off Ver
    Excel 2010
    Posts
    58

    If text is of specific formt change another column colour to grey and add text in another

    Hallo again,

    I need to do the following in MS Excel 2013

    If text in columns Z-AL is black bold underlined or grey italics, change cell in columns AN and AO dark grey.
    and
    If the text is black bold underlined or grey italics, then add "NO" in column AM

    However, if the text in columns Z-AL is normal text, then add "YES" in column AM

    I am attaching and example of the workbook. Sheet 1 contains an extract of the original and sheet 2 an example of the expected outcome.
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,369

    Re: If text is of specific formt change another column colour to grey and add text in anot

    You won't be able to do this with formulae, so I've moved your thread to the VBA section.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: If text is of specific formt change another column colour to grey and add text in anot

    There is no text in Z-AL?
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  4. #4
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: If text is of specific formt change another column colour to grey and add text in anot

    Your workbook might be easier for data entry if you did it the other way around, i.e. enter text in column AM and the cell format changes.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  5. #5
    Registered User
    Join Date
    08-08-2014
    Location
    Lancaster, PA
    MS-Off Ver
    2016 (windows & mac)
    Posts
    94

    Re: If text is of specific formt change another column colour to grey and add text in anot

    I took a stab at it.

    the below code works with cols Z > AL per your original post, although as others have mentioned there is currently no data in those cols, but you can update the WorkingRng variable to suit your needs. I have also limited the working range to the first 35 rows, again update to suit.

    you can update the WorkingRng variable in the SetWorkingRng() sub at the bottom of the code.

    Please Login or Register  to view this content.
    this code uses the SelectionChange event so it will evaluate the current text/formatting whenever the user changes cell. it evaluates all cells in the current row and if one of them matches your criteria it will make the necessary data/formatting changes to cols AM:AO.

    note: I have used the grey text color (Black, Text 1, Lighter 35%) that you had in your example file, this code will only match text that has this exact same color so make sure you always use the same grey!

    not sure how familiar you are with running custom code as I see you originally posted in the formulas & functions forum so below are some basic instructions in case you need them

    open the VBA editor, you should see a list of worksheets on the lefthand side; double click the worksheet that you want this code to work on, it should open a code module window. paste this code into that window and then close the VBA editor, the code will then run anytime a user changes cells. note you will need to save this file as a macro-enabled workbook and enable macros when prompted upon opening.

    I have also attached an updated file with the code already embedded.
    Attached Files Attached Files
    Last edited by thatandyward; 07-09-2017 at 07:25 PM.

  6. #6
    Registered User
    Join Date
    01-22-2014
    Location
    Pretoria, South Africa
    MS-Off Ver
    Excel 2010
    Posts
    58

    Re: If text is of specific formt change another column colour to grey and add text in anot

    Thank you

    All the worksheets in my workbook contains another VBA code which I am using for multiple pick lists in some of the columns. When I add the new code I receive an error message.

    I am attaching a screenshot of the error, as well as a notepad containing both VBA's.

    Please advise.

    Regards,
    Louisa
    Attached Images Attached Images
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    08-08-2014
    Location
    Lancaster, PA
    MS-Off Ver
    2016 (windows & mac)
    Posts
    94

    Re: If text is of specific formt change another column colour to grey and add text in anot

    ah, didn't realize you already had code in the Worksheet module.

    the error is caused by duplicate Option Explicit statements; it's a general statement you put at the start of code modules to set a specific parameter, however you're not allowed to try and set it twice, or have it anywhere other than at the start of the code module.

    the code you already had in there also had an Option Explicit statement, pasting in my code meant there were now 2 Option Explicit statements.

    if you delete the Option Explicit that follows the SetWorkingRng sub and is before Worksheet_BeforeDoubleClick that should take care of the issue.

  8. #8
    Registered User
    Join Date
    01-22-2014
    Location
    Pretoria, South Africa
    MS-Off Ver
    Excel 2010
    Posts
    58

    Re: If text is of specific formt change another column colour to grey and add text in anot

    Good day,

    I created a test workbook, copied the VBA code, removed the second Option Explicit and adapted the working range.

    When trying to run the code, nothing happened and I also did not receive an error message.

    I am attaching the workbook for your perusal please.

    The working range in the workbook is now as follows:

    If text in columns X-AJ is black bold underlined or grey italics, change cell in columns AL and AM dark grey.
    and
    If the text is black bold underlined or grey italics, then add "NO" in column AK

    However, if the text in columns X-AJ is normal text, then add "YES" in column AK
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    08-08-2014
    Location
    Lancaster, PA
    MS-Off Ver
    2016 (windows & mac)
    Posts
    94

    Re: If text is of specific formt change another column colour to grey and add text in anot

    hello louisa,

    looks like you adapted the WorkingRng to cols AI-AU rather than X-AJ.

    Please Login or Register  to view this content.
    should be

    Please Login or Register  to view this content.
    note: you'll need to save, close and re-open the file after you've made the changes to the code, WorkingRng is only defined/stored when it's not currently set to anything, so it won't automatically re-set to the new values until it's cleared from memory.

  10. #10
    Registered User
    Join Date
    01-22-2014
    Location
    Pretoria, South Africa
    MS-Off Ver
    Excel 2010
    Posts
    58

    Re: If text is of specific formt change another column colour to grey and add text in anot

    Thanks,

    For my own clarity what does Cells(4, 24).Resize(32, 13) mean?

    If I understand what it means, I will be able to set the correct working range for each of the sheets where the code will be used.

    Regards,
    Louisa

  11. #11
    Registered User
    Join Date
    08-08-2014
    Location
    Lancaster, PA
    MS-Off Ver
    2016 (windows & mac)
    Posts
    94

    Re: If text is of specific formt change another column colour to grey and add text in anot

    when using the Cells Property to refer to a Range Object in VBA you're essentially saying the cell at the intersection of (Row #4, Column #24) i.e. cell X4.

    column numbering is as you would think, col A = 1, col B = 2, col C = 3… one handy tip for figuring out column numbers when you get into the double letter columns is that you can put the formula =Column() into a cell on a worksheet to return the column number.

    the .Resize function then resizes the Range Object, cell X4, by (32 Rows, 13 Columns), resulting in the Range Object 32 rows tall x 13 columns wide, which produces a Range of X4:AJ35.

    it's possible to define ranges a number of different ways. the line Set WorkingRng = Range("X4:AJ35") would work equally as well in your situation, and is arguably a little clearer. I just tend to default to the Cells Property out of habit when I write code!

    the Cells Property can be useful when defining the range dynamically with changing variables or if you need to iterate a loop over the cells; however in this case your range is fixed so it's equally as valid to refer to it with A1 notation.

  12. #12
    Registered User
    Join Date
    01-22-2014
    Location
    Pretoria, South Africa
    MS-Off Ver
    Excel 2010
    Posts
    58

    Re: If text is of specific formt change another column colour to grey and add text in anot

    Good day,

    i am trying to test the code in the workbook that you provided, but when I click on run nothing happens...not even an error message.

    I copied the code into my own workbook and had the same problem.

    Please advise....

    Regards,

    Louisa

  13. #13
    Registered User
    Join Date
    08-08-2014
    Location
    Lancaster, PA
    MS-Off Ver
    2016 (windows & mac)
    Posts
    94

    Re: If text is of specific formt change another column colour to grey and add text in anot

    there are a couple of possibilities...

    1) the cells being selected/changed are outside of the WorkingRng

    the code is triggered whenever a new cell is selected - the code is written so that it will exit without making any changes if the cell selection falls outside of the defined WorkingRng; this is to prevent the workbook running the code unnecessarily so there is less chance of the workbook becoming sluggish or slowing down.

    I know there was some confusion as to what cells you wanted the code to evaluate - the initial workbook I uploaded had cells Z1:AL35 as the WorkingRng, per your original email; however on subsequent messages you revised that range to be cells X4:AJ35

    I would just make sure whichever version of the workbook you're trying to test has the same area defined as the area you're changing/selecting; if there is a discrepancy then nothing will happen.

    2) the font color exactly matches

    the code uses very specific font color specifications, based on the grey font color in your example workbook, if your text is a slightly different shade of grey then it will not trigger the changes. this is likely less of an issue for the black font criteria as it's much more likely that the automatic/black color is selected for this color, as opposed to the grey where there are multiple, very similar color options

    3) you have macro's enabled on your machine

    am sure you do, but just thought I'd mention on the off-chance

    =====

    this code works fine for me, on both my original file as well as the test file you uploaded in post #8 - I have re-attached that test file, with the WorkingRng updated to your revised range (X4:AJ35) and confirmed again today that it is all working and behaving as I understand it's supposed to.

  14. #14
    Registered User
    Join Date
    08-08-2014
    Location
    Lancaster, PA
    MS-Off Ver
    2016 (windows & mac)
    Posts
    94

    Re: If text is of specific formt change another column colour to grey and add text in anot

    Quote Originally Posted by Louisa Venter View Post

    ...but when I click on run nothing happens...
    I just noticed you said "...when I click on run..."

    this code runs whenever a cell is selected, so navigating around the sheet with either the arrows or a mouse will trigger the code to run, as I mentioned above, it will only do something if the selected cell is inside the desired range. there is no need to run a macro.

    if you bring up the run macros dialog box, you'll only see the SetWorkingRng sub-routine, and running that will have no effect as all it does is store the desired range into memory for use by the main code, which triggers when you change the cell selection

    I should have declared this sub as Private as it's not designed to be visible/run from the macro dialog box. I have removed the attachment from my previous post & re-attached the file here with this sub declared as private
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    01-22-2014
    Location
    Pretoria, South Africa
    MS-Off Ver
    Excel 2010
    Posts
    58

    Re: If text is of specific formt change another column colour to grey and add text in anot

    Thank you for your patience

    I tested the code in the copy of the workbook that you provided and noticed the following:


    If text in columns X-AJ is black bold underlined or grey italics, change cell in columns AL and AM dark grey. Cells columns AM and AN turns grey in stead of cells in column AL and AM.
    and
    If the text is black bold underlined or grey italics, then add "NO" in column AK The No appears in column AL instead of Column AK

    However, if the text in columns X-AJ is normal text, then add "YES" in column AK The Yes does not appear in column AK. I assume that it is also trying to appear in column AL, but AL already has text in.

    I am attaching my test. I renamed the workbook to: LV_TestWorkbook_Example.

    Please advice how I can fix this.

    Regarding the ranges I am working with in my workbook:

    I have a sheet with 109950 rows filled with text. Is this range setting correct? Set WorkingRng = Range("X4:AJ109950")

    I have multiple other sheets where I use the same column range but the rows on each sheet differ from 5 rows to 440800 rows in some instances. I will therefore have to change the working range for each sheet where I need to run the code.


    Regards,
    Louisa
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    08-08-2014
    Location
    Lancaster, PA
    MS-Off Ver
    2016 (windows & mac)
    Posts
    94

    Re: If text is of specific formt change another column colour to grey and add text in anot

    changing which columns go grey and have yes/no values is pretty straightforward; I'm on vacation at the moment so will make the amends/explain how to make changes for you in the next day-or-so if that's ok.

    one point I wanted to clarify though - I was under the impression that you needed this to be 'live updating' i.e. the changes take effect as you edited the document; however you mention above needing to process other workbooks which contain 110K rows and 440k rows, which sounds more like a one-time operation.

    can you clarify if you need this to be a one-time operation or something which live-updates.

    currently it's coded so it will process each row as you select them whilst inputting/editing data; however this is probably not the best approach if you have a large qty of rows to process. am happy to re-write this to be a one-time operation - it's a pretty simple change, but wanted to get some clarity first.

  17. #17
    Registered User
    Join Date
    01-22-2014
    Location
    Pretoria, South Africa
    MS-Off Ver
    Excel 2010
    Posts
    58

    Re: If text is of specific formt change another column colour to grey and add text in anot

    Hi there,

    Timelines is ok with me. Hope the holiday is great!

    I actually need both. For now I need to be able to do a once-off bulk update in preparation of a project that will be starting on 7 August. However, while we will be working on the project, we expect that the user interaction will lead to additions to the spreadsheet, which would then require a continuous "live updating"

    I assume that the to be developed once-off bulk update code will have to be replaced with the current "live updating" code when I am done with the bulk update?

  18. #18
    Registered User
    Join Date
    08-08-2014
    Location
    Lancaster, PA
    MS-Off Ver
    2016 (windows & mac)
    Posts
    94

    Re: If text is of specific formt change another column colour to grey and add text in anot

    Hello Louisa,

    Attached is an updated workbook with some revised code in to hopefully do what you need.

    To avoid any confusion as to which cols are in being tested and which display the results I have added 5 lines of code at the top of the module where you can define the required cols & first row of data.

    Please Login or Register  to view this content.
    I thought this might also be useful in case you ever need to adjust which columns to process in other workbooks.

    The revised code automatically adjusts the WorkingRng to suit the amount of data in the sheet - this will update if data is added/deleted, so no need for you to do anything for that.

    Rather than having separate code for the initial processing, the revised code will work for both initial processing and the live updating. Essentially it works by checking each row in the current selection; so when you want to do the initial processing just select an entire column and the code will process all rows in that selection.

    Technically it checks the old selected cell(s) as the user moves to a new selected cell(s). This allows the code to run even if all the user has done is adjusted the formatting. The one quirk for this is that the first time the user makes a selection after opening the workbook nothing will happen as the previous selected cell has not yet been loaded into memory.

    A word of caution, processing the number of rows you're talking about will take a fair amount of time. I have written the code to be as efficient as possible to minimize the time taken. I replicated your data down to about 50K rows and it took about 140-150secs to process on my machine, so running on 110K or even 440k rows will take some time - it's also possible we might hit some of Excel's software limits - again I have written the code to try and mitigate these but I only tested up to 50K rows.

    Just so you know, the existing code in the example workbook you posted, was causing the project to not compile --there seemed to be missing variable declarations; rather than making changes to code I didn't write I opted to remove it from my updated example workbook - so you'll need to paste that back in and have whoever wrote it look at it if it's causing issues.

    in case anyone else is following along at home below is the code - it's all in the Worksheet Module

    Please Login or Register  to view this content.
    Attached Files Attached Files

  19. #19
    Registered User
    Join Date
    08-08-2014
    Location
    Lancaster, PA
    MS-Off Ver
    2016 (windows & mac)
    Posts
    94

    Re: If text is of specific formt change another column colour to grey and add text in anot

    Louisa,

    Another possible optimization occurred to me last night - I updated the code this morning and it appears to help reduce running time when dealing with large # of rows - it reduced the running time on 50k rows from ~140secs to ~110sec, so I figured probably worth updating as this could have significant impact when you're dealing with 440k rows.

    Updated example workbook attached & updated code below

    Please Login or Register  to view this content.
    Explanation: to minimize the number of writes to the worksheet, each criteria match is added to a range, thereby allowing a single write operation at the end of the loop; However despite being faster than continually writing to the worksheet it still takes considerable time to set the range on each match (~27k matches in the test data of 50k rows).

    To minimize the number of Set Range operations the matches are stored in a string of col & row#s "$AL$345,$AL$348,$AL$349,$AL$343..." which is then used to set the range. I had initially hoped to only need to set the range once with this technique, but there is a 255 character limit for strings used as function arguments. Therefore a collection is used to store the strings as they reach the 255 character limit. At the end of the loop the collection is iterated over to set the range, this reduces the # of Set Range operations from ~27k to about 900.

    However I realized that I was using the .Address property when appending the current row to the string, which by default also includes $'s in the address string

    Please Login or Register  to view this content.
    => "$AL$345,$AL$348,$AL$349,$AL$343..."

    by building the address using the column letter and row number it stores the address without the $'s, thereby saving 2 characters per matching row.

    Please Login or Register  to view this content.
    => "AL345,AL348,AL349,AL343..."

    This reduces the # of Set Range operations to about 600, which saved about 30sec with the test data.
    Attached Files Attached Files
    Last edited by thatandyward; 08-08-2017 at 07:55 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 7
    Last Post: 05-04-2017, 02:09 PM
  2. Conditional Formt IF cell contains formula with specific text
    By noitidart in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-19-2012, 08:36 PM
  3. [SOLVED] Cells to change colour depending on specific text
    By Fran Habbitts in forum Excel General
    Replies: 4
    Last Post: 12-11-2012, 10:50 AM
  4. [SOLVED] Macro to change text colour of adjacent cell to the same text colour as target cell
    By kayoke in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 06-28-2012, 04:18 AM
  5. Replies: 2
    Last Post: 06-07-2011, 07:00 AM
  6. change fill colour row when specific text entered
    By gobbolino in forum Excel General
    Replies: 4
    Last Post: 05-13-2007, 10:18 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1