#  Other Applications & Softwares  > Outlook Formatting & Functions >  > [SOLVED] Formula only works on first cell specified in range

## Bensley

Hello

I am making a calendar for my band, and i have an overview page then a page for each month. What i want to happen is when i enter something on the detailed page it changes the fill of the according day on the overview. With the formula's i am using it is only working for the first cell and not the rest specified in the range.

Here are my formulas

=ISTEXT(January!B5:B52)    (grey fill)         applies to =$C$2
=NOTISTEXT(January!B5:B52)       (no fill)      applies to =$C$2


2018-03-20 (1).png

2018-03-20.png

*Edit*
Working on first cell

2018-03-20 (2).png

2018-03-20 (3).png

Not working on other cells

2018-03-20 (4).png

(see comments for other photo)

Any ideas ? 

Thanks 

Bensley

----------


## kersplash

Can you attach a sample workbook with what you expect to be displayed?

----------


## Bensley

Other photo

Attachment 566434

----------


## Bensley

I have updated it, there is a photo in the comment above as i can only upload 5 in the post

----------


## kersplash

Attaching a workbook would be much more helpful. My eyes aren't what they used to be.  :Smilie:

----------


## sandy666

maybe try =ISNONTEXT(value)

something like (except UDF) NOTISTEXT doesn't exist or you forgot brackets: NOT(ISTEXT(value))

----------


## Bensley

> Attaching a workbook would be much more helpful. My eyes aren't what they used to be.





I can't seem to find the attach document??

----------


## Bensley

> maybe try =ISNONTEXT(value)
> 
> something like (except UDF) NOTISTEXT doesn't exist or you forgot brackets: NOT(ISTEXT(value))



The NOTISTEXT is just there to change it back to clear if we remove what was written, it is working on first cell only as well

----------


## kersplash

To attach - click Go Advanced then Manage Attachments then Upload.

----------


## Bensley

Here you go  :Smilie:

----------


## sandy666

> The NOTISTEXT is just there to change it back to clear if we remove what was written, it is working on first cell only as well



It's not necessary because if in cell will be nothing CF return value 0 so it means doesn't change color.
CF works with TRUE or FALSE (1 or 0) and nothing more

----------


## kersplash

Try this as the only rule to replace the ones you have;

Capture.JPG

----------


## Bensley

> It's not necessary because if in cell will be nothing CF return value 0 so it means doesn't change color.
> CF works with TRUE or FALSE (1 or 0) and nothing more



Yes but it meant that if i cancelled or moved something i had to go change the colour back to blue or what ever manually, when you deleted text it would just unlock the colour allowing me to change it again, using the not, made it change it back

----------


## Bensley

> Try this as the only rule to replace the ones you have;
> 
> Attachment 566438



That worked perfectly

Do you know of any short cuts (i know excel is meant to be fairly good at learning) that would make applying that formula to each day quicker, or is it just slog through it

----------


## kersplash

I don't know, but I hope for your sake, since you have to have a rule for every single day.

----------


## Bensley

So I worked out how to get it to copy across so that i just have to change it for each month (as i am referencing a different page) using

Formula   


```
Please Login or Register  to view this content.
```


(Font)      
Applies to 


```
Please Login or Register  to view this content.
```




But now i have my original problem, i have all days highlighting in the month that i have done (January) but if i choose any time slot other then 00:00 it won't register, what have i done wrong ?

----------

