Conditional formatting

[stephen.leonard3]

Hi
I am trying to create a holiday planner for the GP practice where I
work, and have have inserted a number for available appointments a GP
has in cell B1, but when they go on holiday I insert a H in cell A1 and

it turns yellow, and in cell B1 I want it to change to overide the
number of appointments by placing a 0 and also turning it yellow, I can

get the colour to change but not the number, I have used formula is:
=IF(A1="H",0) but it is not working, it works as a basic if function
but not in conditional formatting, please help.

Thank You


Pam

 

[Pete_UK]

As this is conditional formatting, you don't need the IF bit. Select
B1, then Format | Conditional Formatting and choose Formula Is rather
than Cell Value is. Then enter this formula:

=(A1="h")

then click on Format, Patterns tab and choose yellow as the background
colour and OK your way out of it. Excel often puts extra quotes in the
formula, so if the background colour does not change when you have H in
A1, then check the formula again and edit if necessary - it should be
exactly as shown above.

Hope this helps.

Pete

 

[Pete_UK]

I should have pointed out that it is case-insensitive, so you could
just as well have:

=(A1="H")

and both will work if you have H or h in A1.

Pete

 

[Guest]

Pete,
... and the question was .. ?? <g>
I'm reading this thread from MS Office Online, where I see nothing except
the OP's subject line: Conditional formatting. The OP is also "unknown", but
of course <g>

---

 

[David McRitchie]

I see he first two in the thread as replies by Pete, wonder if it is
an old thread and even more than two months old so we don't even
see it in a newsgroup. Don't know if that is the problem, but Google
doesn't even warn someone if the thread is old. With such a common
subject -- chances are Google has overlapped many threads and you
won't really be able to tell mcuh.

 

[Guest]

I see the first two in the thread as replies by Pete, wonder if it is
an old thread and even more than two months old so we don't even
see it in a newsgroup. Don't know if that is the problem, but Google
doesn't even warn someone if the thread is old. With such a common
subject -- chances are Google has overlapped many threads and you
won't really be able to tell mcuh.

Thanks, David. With your hint, I found the "complete" thread in Google,
and it's with the OP intact (it's a new thread <g>) ..

---- OP's query --------
From: (e-mail address removed)
Newsgroups: microsoft.public.excel.newusers
Subject: Conditional formatting
Date: 31 May 2006 05:05:26 -0700

I am trying to create a holiday planner for the GP practice where I
work, and have have inserted a number for available appointments a GP
has in cell B1, but when they go on holiday I insert a H in cell A1 and

it turns yellow, and in cell B1 I want it to change to overide the
number of appointments by placing a 0 and also turning it yellow, I can

get the colour to change but not the number, I have used formula is:
=IF(A1="H",0) but it is not working, it works as a basic if function
but not in conditional formatting, please help.

 

[dmcritchie_xlmvp]

Original message appears okay in Google, and it was posted through
Google Groups by Stephen Leona and not an old message either.
Pete's replies were also both posted via Google
Max's reply was posted using Microsoft CDO
My reply was posted using OE to direct to Microsoft newsserver, and
I can't see the originl message from Stephen at all in OE connected
directly to
Microsoft servers.
My second reply (this post) was posted using Google Groups.

So there was no old post involved, and no intertwined threads
shown in Google either, just another one of those mysteries.

The entire thread as seen in Google can be see at:
http://groups.google.com/groups?threadm=1149077126.133240.149290@i39g2000cwa.googlegroups.com

As I see the question, you could select columns A:B with cell A1 as
the
active cell then use Formula is =$A1="H", if fact if you wanted
the
entire row to turn yellow you could select all cells on the sheet and
as long as the active cell is on row 1, use the same formulas, and the
entire row would turn yellow based on the value in the A column.

Conditional Formatting
http://www.mvps.org/dmcritchie/excel/condfmt.htm

HTH, David McRitchie, Microsoft MVP, Excel

 

[dmcritchie_xlmvp]

Replying to Max's second reply his again posted via Microsoft CDO, and
while I was waiting for my second message to appear (this was also
posted via Google Groups), I'm going to have to fix my friendly name
in Google Gorups for future use. Remains a mystery why some postings
are not seen by everyone.
-- David McRitchie

 

[David McRitchie]

Google Groups is not displaying sub threading correctly, this
reply is from OE to Pete's first reply. So this post should be
indented under 2nd reply (1st post that I see in OE) and should
appear at the bottom. The really bizarre thing is that OE is also
forced to show the same appearance order and indentation.
The numbers are actually in chronological order you will only see
the numbers in Google.

Conditional formatting as seen in Google Groups when this was posted
1 (e-mail address removed) May 31 [Mozilla] 12:05:31 GMT
2 Pete_UK May 31 (reply to #1) [Mozilla]
3 Pete_UK May 31 (reply to #2) [Mozilla]
4 Max May 31 (reply to #3)
5 David McRitchie May 31 (reply to #4)
6 Max Jun 1 (reply to #5)
7 (e-mail address removed) Jun 1 (reply to #5)
8 (e-mail address removed) Jun 1 (reply to #6)
9 David McRitchie (reply to #2, as a test) think it will look like this

Don't know about the numbers in front but indentations and order should appear as
6 Max Jun 1 (reply to #5)
8 (e-mail address removed) Jun 1 (reply to #6)
7 (e-mail address removed) Jun 1 (reply to #5)
9 David McRitchie (reply to #2, as a test)









--

 

[Pete_UK]

I'm sorry I caused such confusion by responding to a thread !!

As David points out, I used Google Groups, and this is where I saw the
OP - I thought it was strange that no-one had responded to such a
fairly straightforward problem, although the post was half a day old.

I've also found in the past that some of my posts were not reproduced
in some other groups, notably Excel Forum - I haven't a clue why (nor
did I understand how they got there in the first place).

Pete

 

[stephen.leonard3]

Pete

I dont think I have explained my problem very well, basically I've
entered the number 33 in cell B1 and want it to change automatically to
a 0 when entering a letter H in A1,the replies I have recieved are
telling me to enter a IF fuction in cell B1 but by doing this my number
33 goes and I need these numbers for a total.

Thanks

Pam

 

[Sandy Mann]

I am somewhat reluctant to post a response to this question in case it has
been answered and I just cannot see it but right-click on the sheet tab and
select View Code then paste this cod into the window that will open up:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

'If entry not in column A then do nothing
If Target.Column <> 1 Then Exit Sub

'If deleting the letter in column A then do nothing
If Target.Value = "" Then Exit Sub

Cells(Target.Row, 2).ClearContents

End Sub


--
HTH

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Pete_UK]

Pete

I dont think I have explained my problem very well, basically I've
entered the number 33 in cell B1 and want it to change automatically to
a 0 when entering a letter H in A1,the replies I have recieved are
telling me to enter a IF fuction in cell B1 but by doing this my number
33 goes and I need these numbers for a total.

Thanks

Pam

I thought your problem was about Conditional Formatting, but thanks for
the clarification. What you are asking now, though, seems a bit like
having your cake and eating it - you want the cell to show 0, but you
want to retain the value of 33 that you have put in there!

You could apply conditional formatting to the cell to set the
foreground colour to the same as the background colour (yellow?) if A1
= "H", so that it would appear to be empty, but the value would be as
it was - hence your totals would still apply.

Hope this helps.

Pete