#1
|
|||
|
|||
VBA to extract/hide text by font color?
Would be grateful if one could help me with a code for this situation I am facing with daily:
- Bilingual excel files with thousands of lines - I need to show only the text from column A that corresponds(by row) to the red text from B or to extract it separately. - Now I manually hide everything except this needed text. This is a very tedious job on a daily basis. So a code to extract just the text mentioned above from A(by color in B) and place it in B(override) or into another column, but necessarily at the same position, same line as in A, would be heaven Or a code to automatically hide everything except that needed text would be also awesome Thanks! Last edited by tyxanu; 09-01-2020 at 05:13 AM. |
#2
|
||||
|
||||
The way I would do this is to add a third column containing a formula which reports on whether the second column is formatted as Red or not. I don't know if there is a built-in formula that can do this so I added one as a VBA custom function.
Once you have a column that returns either true or false for your trigger, you can convert the content to a table and use the filter dropdowns to hide the black or red rows. The custom function is: Code:
Function RedCell(aRng As Range) As Boolean RedCell = aRng.Font.Color = RGB(255, 0, 0) End Function =RedCell(B2)
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#3
|
|||
|
|||
So I am trying to create a button from scratch based on your indications
And I am getting "Compile Error: Expected End Sub". Please note that i am relatively new to VBA in Office What am I doing wrong in this file below? |
#4
|
||||
|
||||
You don't need a button on the sheet. What do you want that button to do for you?
Look at the file I posted. Don't go adding more than I put into the file until you understand how it works.
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
#5
|
|||
|
|||
Ohh, now I understand
Yes, indeed. It works. You are God! As for different colors, I just have to change the code "(255, 0, 0)" to the one I need,right? |
#6
|
||||
|
||||
Now you want to start changing colours - this is beginning to sound like you are doing the same VBA assignment as John_4. Have a look at this thread https://www.msofficeforums.com/word-...footnotes.html.
As per that thread, if you want something more robust and less error prone, convert to using styles to avoid the RGB number sensitivities. Record some macros of applying a style and see if you can work out how to adapt the Function so it returns the name of a style instead.
__________________
Andrew Lockton Chrysalis Design, Melbourne Australia |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How to extract text between <> | LearnerExcel | Excel | 4 | 02-07-2018 06:11 AM |
Formula to Extract text from a text string | Haha88 | Excel | 2 | 11-14-2017 01:32 AM |
Advanced Font Color Manipulation In Word: Inserting another color than the surrounding text | stuartg | Word | 1 | 02-20-2017 12:38 AM |
Text Field [content control] - Default text color vs Filled Text color | jackcoletti | Word | 3 | 02-01-2017 08:10 AM |
Remove white text background (keeping the font color and page color intact | cc3125 | Word | 1 | 10-26-2015 06:44 PM |