Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-01-2020, 12:52 AM
tyxanu tyxanu is offline VBA to extract/hide text by font color? Windows 8 VBA to extract/hide text by font color? Office 2013
Novice
VBA to extract/hide text by font color?
 
Join Date: Aug 2020
Posts: 13
tyxanu is on a distinguished road
Default 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!
Attached Files
File Type: xlsx test.xlsx (9.0 KB, 9 views)

Last edited by tyxanu; 09-01-2020 at 05:13 AM.
Reply With Quote
  #2  
Old 09-01-2020, 04:50 PM
Guessed's Avatar
Guessed Guessed is offline VBA to extract/hide text by font color? Windows 10 VBA to extract/hide text by font color? Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

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
Once that macro is in your workbook, you can use a formula in any cell like
=RedCell(B2)
Attached Files
File Type: xlsm LanguageFiltering.xlsm (18.7 KB, 6 views)
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #3  
Old 09-02-2020, 03:54 AM
tyxanu tyxanu is offline VBA to extract/hide text by font color? Windows 8 VBA to extract/hide text by font color? Office 2013
Novice
VBA to extract/hide text by font color?
 
Join Date: Aug 2020
Posts: 13
tyxanu is on a distinguished road
Default

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?
Attached Files
File Type: xlsm test.xlsm (18.4 KB, 6 views)
Reply With Quote
  #4  
Old 09-02-2020, 04:22 AM
Guessed's Avatar
Guessed Guessed is offline VBA to extract/hide text by font color? Windows 10 VBA to extract/hide text by font color? Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

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
Reply With Quote
  #5  
Old 09-02-2020, 04:38 AM
tyxanu tyxanu is offline VBA to extract/hide text by font color? Windows 8 VBA to extract/hide text by font color? Office 2013
Novice
VBA to extract/hide text by font color?
 
Join Date: Aug 2020
Posts: 13
tyxanu is on a distinguished road
Default

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?
Reply With Quote
  #6  
Old 09-02-2020, 03:17 PM
Guessed's Avatar
Guessed Guessed is offline VBA to extract/hide text by font color? Windows 10 VBA to extract/hide text by font color? Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

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
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA to extract/hide text by font color? 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
VBA to extract/hide text by font color? Text Field [content control] - Default text color vs Filled Text color jackcoletti Word 3 02-01-2017 08:10 AM
VBA to extract/hide text by font color? Remove white text background (keeping the font color and page color intact cc3125 Word 1 10-26-2015 06:44 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 01:13 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft