Hi there, at work I export files to excel from a database called Anaplan. Some of the values in the export have blue font.
I need to do an IF statement based on the font color in cells - i.e. if that row contains any cells with blue font then give me this, if not (i.e. if that row only contains cells with black font then give me that.
Please see attached.
Can it be done without VBA?
I have Office 365.
Thanks
Short answer: No.Can it be done without VBA?
Long answer: Colour is not data, so no.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
You could use GET.CELL , which is a little difficult to master..
I can't get CELL or GET.CELL to work. Perhaps someone can post an example?
This function seems to work.
Courtesy: https://www.sulprobil.com/sbgetcell_en/
Formula:
Please Login or Register to view this content.
Please Login or Register to view this content.
Get.Cell must also be saved into .xlsm
An example like this,
1. create sheet2 tab.
2. Ctrl+F3, New
3. Name: getColor Refers to:=GET.CELL(24,Sheet1!A1)
4. sheet2!A1 =getColor
5. copied down and across.
6. sheet1!E2 =IF(COUNTIF(Sheet2!B3:C3,5)>0,"Anaplan","DCRM")
7. copied down.
This also seems to work with GET.CELL but 4.0 macros have to be enabled via the Trust Center
Thanks everyone but I was shown another way to isolate the blue font projects in the program the data came from (Anaplan)! I'm still learning macros so struggled with some of the above. I'll revisit this post though and when not so busy and try to work it out! Thanks again
You're welcome. Thanks for the rep.
/......../
Last edited by L0renz; 08-31-2023 at 11:01 AM. Reason: error ... wrong thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks