A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Programming
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

conditional formatting, bleh



 
 
Thread Tools Display Modes
  #1  
Old January 4th 17, 04:51 PM posted to microsoft.public.excel.programming
Matthew Dyer
external usenet poster
 
Posts: 175
Default conditional formatting, bleh

trying to use this formula for conditional formatting in column D:
=NOT(ISNA(VLOOKUP(D1, SLS_Holidays, 1, FALSE)))

where any value in D that is found in SLS_Holidays will be highlighted. It wont accept copy/paste, and attempts at modifying it are not getting me anywhere. please help.
Ads
  #2  
Old January 4th 17, 05:04 PM posted to microsoft.public.excel.programming
Claus Busch
external usenet poster
 
Posts: 3,448
Default conditional formatting, bleh

Hi Matthew,

Am Wed, 4 Jan 2017 08:51:23 -0800 (PST) schrieb Matthew Dyer:

> trying to use this formula for conditional formatting in column D:
> =NOT(ISNA(VLOOKUP(D1, SLS_Holidays, 1, FALSE)))


try in CF:
=COUNTIF(SLS_Holidays,D1)>0

Regards
Claus B.
--
Windows10
Office 2016
  #3  
Old January 4th 17, 05:11 PM posted to microsoft.public.excel.programming
Matthew Dyer
external usenet poster
 
Posts: 175
Default conditional formatting, bleh

On Wednesday, January 4, 2017 at 10:04:22 AM UTC-7, Claus Busch wrote:
> Hi Matthew,
>
> Am Wed, 4 Jan 2017 08:51:23 -0800 (PST) schrieb Matthew Dyer:
>
> > trying to use this formula for conditional formatting in column D:
> > =NOT(ISNA(VLOOKUP(D1, SLS_Holidays, 1, FALSE)))

>
> try in CF:
> =COUNTIF(SLS_Holidays,D1)>0
>
> Regards
> Claus B.
> --
> Windows10
> Office 2016


so I must be doing something wrong - when I copy/paste your formula directly into CF I get an broken formula error. I take out the = and hit OK, it puts it back in, and surrounds the formula with "s
  #4  
Old January 4th 17, 05:28 PM posted to microsoft.public.excel.programming
Claus Busch
external usenet poster
 
Posts: 3,448
Default conditional formatting, bleh

Hi Matthew,

Am Wed, 4 Jan 2017 09:11:47 -0800 (PST) schrieb Matthew Dyer:

> so I must be doing something wrong - when I copy/paste your formula directly into CF I get an broken formula error. I take out the = and hit OK, it puts it back in, and surrounds the formula with "s


in CF you cannot use sheet scope names. Is you name a global name, a
workbook scope name?


Regards
Claus B.
--
Windows10
Office 2016
  #5  
Old January 4th 17, 05:59 PM posted to microsoft.public.excel.programming
Matthew Dyer
external usenet poster
 
Posts: 175
Default conditional formatting, bleh

On Wednesday, January 4, 2017 at 10:28:30 AM UTC-7, Claus Busch wrote:
> Hi Matthew,
>
> Am Wed, 4 Jan 2017 09:11:47 -0800 (PST) schrieb Matthew Dyer:
>
> > so I must be doing something wrong - when I copy/paste your formula directly into CF I get an broken formula error. I take out the = and hit OK, it puts it back in, and surrounds the formula with "s

>
> in CF you cannot use sheet scope names. Is you name a global name, a
> workbook scope name?
>
>
> Regards
> Claus B.
> --
> Windows10
> Office 2016


workbook scope - it's a table name
  #6  
Old January 4th 17, 06:18 PM posted to microsoft.public.excel.programming
Matthew Dyer
external usenet poster
 
Posts: 175
Default conditional formatting, bleh

On Wednesday, January 4, 2017 at 10:59:28 AM UTC-7, Matthew Dyer wrote:
> On Wednesday, January 4, 2017 at 10:28:30 AM UTC-7, Claus Busch wrote:
> > Hi Matthew,
> >
> > Am Wed, 4 Jan 2017 09:11:47 -0800 (PST) schrieb Matthew Dyer:
> >
> > > so I must be doing something wrong - when I copy/paste your formula directly into CF I get an broken formula error. I take out the = and hit OK, it puts it back in, and surrounds the formula with "s

> >
> > in CF you cannot use sheet scope names. Is you name a global name, a
> > workbook scope name?
> >
> >
> > Regards
> > Claus B.
> > --
> > Windows10
> > Office 2016

>
> workbook scope - it's a table name


for the immediate I just hard-coded my range (H2:H20), but I would appreciate if someone could tell me how I could use my named range instead.
  #7  
Old January 4th 17, 06:24 PM posted to microsoft.public.excel.programming
Claus Busch
external usenet poster
 
Posts: 3,448
Default conditional formatting, bleh

Hi Matthew,

Am Wed, 4 Jan 2017 10:18:15 -0800 (PST) schrieb Matthew Dyer:

> for the immediate I just hard-coded my range (H2:H20), but I would appreciate if someone could tell me how I could use my named range instead.


have a look:
https://1drv.ms/x/s!AqMiGBK2qniTgYVbEmzNp--N_HbuEw


Regards
Claus B.
--
Windows10
Office 2016
  #8  
Old January 6th 17, 01:52 PM posted to microsoft.public.excel.programming
Matthew Dyer
external usenet poster
 
Posts: 175
Default conditional formatting, bleh

On Wednesday, January 4, 2017 at 11:24:35 AM UTC-7, Claus Busch wrote:
> Hi Matthew,
>
> Am Wed, 4 Jan 2017 10:18:15 -0800 (PST) schrieb Matthew Dyer:
>
> > for the immediate I just hard-coded my range (H2:H20), but I would appreciate if someone could tell me how I could use my named range instead.

>
> have a look:
> https://1drv.ms/x/s!AqMiGBK2qniTgYVbEmzNp--N_HbuEw
>
>
> Regards
> Claus B.
> --
> Windows10
> Office 2016


Sorry Claus, but it appears to be a broken link
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How can I convert conditional formatting into explicit formatting? Patrick Harris Excel Discussion (Misc queries) 0 April 9th 09 12:00 AM
Conditional formatting--different formatting depending on cell con Tammy S. Excel Discussion (Misc queries) 3 March 30th 09 08:11 PM
Formatting Conditional Formatting Icon Sets The Rook[_2_] Excel Discussion (Misc queries) 3 March 7th 09 08:48 PM
Protect Cell Formatting including Conditional Formatting Mick Jennings Excel Discussion (Misc queries) 5 November 13th 07 05:32 PM
Conditional Formatting that will display conditional data BrainFart Excel Worksheet Functions 1 September 13th 05 05:45 PM


All times are GMT +1. The time now is 06:47 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.