Excel 2007: Conditional formatting
In Excel 2007, I want to create a rule that turns cells red in case
that 3 out of 7 cells in a column equal 0. What formula will catch
this condition? Thanks to the math stars! Sven
I did this with A1:G1
Selected the range; used Conditional Formatting on the Home group ->New
Rule ->Use a formula ...... and entered this
When three or more cells in the range are zero (blanks do not count as zero
here), all cells go red
best wishes from Canada
Bernard V Liengme
Microsoft Excel MVP
remove caps from email
"Sven Berg" <...legend formatting
Is there any way I can have subscript/superscripts in the legend of a chart? If it's not readily available, can a VBA script take care of it?
Unfortunately it is not possible to format individual characters of a
legend entry or an axis tick label. You could make a textbox, which
allows individual character formatting.
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
> Is there any way I can have subscript/superscripts in the legend of a
> chart? If it's not readily available, c...2003 condition
I have a numeric field with values 1,2 or 3 referenced by a label in an array
formula. I thought that previous formula where condition LABEL=1 worked,
but it now it has to be LABEL="1".
Unfortunely it's in a file not frequently used so I can't prove that LABEL=1
So I was wondering if anyone knows if there has been a SP to Excel 2003 that
might now require quotes for a conditional check of a numberic field or is my
memory bad and this never should have worked in the first place.
Thanks in advance for any insight
...Auto Hide/Unhide Formatting
I'm wanting to create conditional formatting such that when users
submit info in one cell, a previously hidden row underneath it becomes
unhidden and available for formatting. Is there a way to do that?
Not using Conditional Formatting.
You would need event code to achieve the unhiding of a row.
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
If Target.Value <> "" Then
Rows("2:2").EntireRow.Hidden = False
What is the difference between a sliding scale vlookup and
a fixed scale vlookup and which one is the best?
Not sure what you mean by "sliding scale" vs "fixed scale"
but I'll take a guess.
The fourth argument of a vlookup can be set to either True or False.
VLookup("what", "where", "index", T/F)
With the argument set to "True" (sliding scale???), your data table
is assumed to be (and definitely should be) in alphanumeric order.
A Vlookup of anything from 200 ...How do I set up the default format for a comment?
I wan't all new excel comments to have the same look by default without
having to edit each comment individually. I'm using Office 2003.
I thought all the comments would have the same look.
But if they don't, Debra Dalgleish has lots of code to format comments when you
> I wan't all new excel comments to have the same look by default without
> having to edit each comment individually. I'm using Office 2003.
...Printing Reports: Force A4 Format 07-19-07
i am having trouble forcing a report to be in the A4 format when
sending to the printer. it happens all the time that the report get
send with the format 'letter' which causes the printer to stop and
request for paper in letter format. the report i generate totally fits
in the A4 format but it won't work the way i want it to! this is
pretty annoying because i always have to manually push a button on the
printer so that the report can be printed. is there a way through any
preferences or vba code to make this work ?
maybe someone can help me or give me a hint. thanks in adv...Format of SSN Field on Form
I have a table with an pkEmpID (auto number) and txtSSN, txtLName, etc. fields.
I have a form with a combo box used to query this table. The combo box
retrieves the pkEmpID and txtSSN when the user types in the SSN. (The pkEmpID
is bound to a field in another table.)
When I type in the SSN, I want the combo box to display it as @@@-@@-@@@@.
If I set the field format to @@@-@@-@@@@ in the table, the SSN displays the
data okay on the form. But you have to type in the dashes as part of the
entry. I prefer not to have to do that.
I took the formatting off the table field and tried to s...Reprint Large format
Does anyone know how I can reprint a Receipt into a different style. I
printed a receipt through a 40 column printer and receipt printer two has a
80 column printer that printed the large format receipt i want... I would
like to be able to print that large format receipt one more time.
You have to have Printer #2 set to Journal Receipts ( in Register
Properties). If this is not set, you won't be able to reprint. If it is,
you can just open the Journal viewer and page up/page down to find the
version you want.
Tiber Creek Consulting
glenn@...multiple font formatting to a single cell
I am trying to use superscript in a cell to just one number not the entire
text in that cell. I can format it ok, and the formatting is there.
However, once I hit the enter key, the formatting to the one number
disappears. I'm new to xp's version of excel. I used to be able to do it
in earlier versions of excel.
Example: salary 2. I only want to add superscript to the number 2.
Is there a way to do this in xp?
...help with header row format
The first row in my excel file is the header. When I insert a new row after
that, the new row takes the format, color etc of the header. How can I avoid
the new rows inserted form taking the format of the forst row?
If it's Excel 2003, when you insert the new row under the header row youy
should get a small icon appearing which has a drop down arrow providing
format options. You can select to format the new row as the line above, the
line below, or clear formatting.
> The first row in my excel file is the header. W...formating within formula
In C10 cell I have the following formula:
="The total percentage is " & A10
Cell A10 is a percentage calculation. The problem I have
is that in Cell C10, the display is something like this:
The total percentage is 0.63447676223746
I know that if this value was a currency, I could have
used the formula ="the total percentage is " & dollar(A10).
This would have gotten rid of extra decimals. What other
formating can I use to only keep two digit in this
percentage within the formula?
Any help is appreciated
="The total percenta...Format Painter in MS Word
Operating System: Mac OS X 10.5 (Leopard)
Processor: Power PC
Hi - How do I set up a keyboard short-cut for Format Painter? (MS Word 2008 for Mac version 12.1.7)
Go into Tools> Customize Keyboard & Menus then click the Keyboard button. On
the Commands page select Edit from the Categories list then locate the
following commands & assign the keystroke of choice to each:
On 5/12/09 10:44 AM, in article email@example.comR9absDaxw,
"Derek100@officeformac.com" <Derek100@o...Link; clipboard format
I would like to extract the executable file name from an icon on the
desktop. I've copied a link to the clipboard but it seems to have a
non-standard clipboard format. Does anyone know how I can do this or where
info on this format might be?
It is a shell clipboard format, so you have to read about the shell extensions. There is a
special clipboard format in the shell for this. It has been enough months since I did this
that I am at this point fairly clueless (I have decided that Shell programming, like
ActiveX, is a Place I Do Not Wish To Go). But perhaps someone who understands...Formatting numbers
How do I format numbers such that they appear like below:
On selecting the "," icon, format the numbers in this way 100 to become
100.00 and if it is negative then (100.00).
Likewise on selecting the "$" icon then display the number in this way 100
to become $100.00 and if it is negative then $(100.00).
The location of the dollar sign should line up down the column irrespective
of the length of the amount. I don't know why Excel doesn't do this by
How do I then get Excel to always do this formatting by default.
Experiment with Format ...Field validation and formatting
How do I validate and format a field? I want to format all phone numbers to
a standard format--(###) ###-#### x###.
However, for standard text boxes, I can not find any event tabs. All I can
find is the onchange evend for pick lists.
Thanks in advance!
I saw this discussed several times that its not modifable. We run CRM in
house but im not the CRM admin so I never verified it I just took the
answer for what its worth.
"Paul McBride" <firstname.lastname@example.org> wrote in
> How do I validate and format a field? I want to format all...Using vlookup against blank cell
I am using vlookup to return values (lets say values A-I) based on other
values (Values 1-10) in column A.
Sometimes there is no value in column A and I'd like vlookup to return a
value "blank cell" or whatever I define.
Right now blank cells return N/A.
I am guessing there is something simply I can do there.
Thanks in advance
In article <#2YO5vFFIHA.4752@TK2MSFTNGP04.phx.gbl>,
"bam" <email@example.com> wrote:
> I am using vlookup to return values (...Conditional formatting 02-11-10
In a cell, I want to allow the value only if a condition is satisfied.
If this condition is not satisfied, and if I format the value to be white,
then the value is still in the cell and cannot been seen because it is white.
This is no good. I want no value in the cell.
You can use Data Validation to prevent the entry of an invalid value.
Or you could use event code to delete the invalid value after entry.
Which way to go is up to you.
Gord Dibben MS Excel MVP
On Thu, 11 Feb 2010 07:35:01 -0800, LP <LP@discussions.microsoft.com> wrote:
...Conditional Formatting in Excel Help Please.....
Newbie to VBA
I need to use Conditional Formatting to format a range of cells to have
Wheat background and Red text (Bold) if a text string is contained in
each cell. For example, all cells in a range that contain the text
string "LLC" or "Inc" or "Corp" or "Corporation" or
"Company" , etc.
If cell E2 contains Acme, LLC
I need to give that cell (E2) the condition formatting
If cell E3 contains John Brown
That cell does not get the condition formatting
If cell E4 contains Joe's Pool Hall, Inc
Cell E4 gets the condition formatting
Thanks ...Wait condition and custom workflow stay waiting (error)
I have a custom workflow that update a field in a custom entity and it works
fine but I need to delay its execution and for that I used a wait condition
previous a this custom workflow. When the workflow (with a wait condition)
exceute stay waiting and throw an error and in the table Workflowlog in the
database show me the error -2147220970
'System.ComponentModel.Container' ..... )
Anyone can help me with this issue ?
A full error message and detail information about the workflow defin...Hindi Number format
Is ther an equivalent in Access for a for a number format available in
.NumberFormat = "[$-2000000]#0,000.00"
So that I can have Hindi numerals in a reports.
when I use Numeral shapes to context or system or National it is not
giving me the desired result.
I want to display the numbers in Hindi format regardless of the system
How I can apply this in a Report so that the field will be formated
using the above way?
...Formatting toolbar won't stay active
I have Windows XP with Office XP, Outlook 2002. When I
reply or forward an e-mail, I select view, toolbars,
advanced and use the formatting buttons in the e-mail.
However, when I reply or forward the next e-mail, the
toolbar has disappeared. Any ideas on what might cause
this or how can I get the tool bar to stay active when I
reply or forward e-mails?
"David" <firstname.lastname@example.org> wrote in message
> I have Windows XP with Office XP, Outlook 2002. When I
> reply or forward an e-mail, I select view, toolbars,
> adv...Converting time formatted cells
I would like to convert time/hour formatted cells to days.
1 day, 4 hours, 59 minutes
How can I do this?
Basically I am trying to add up time formatted cells so I can calculat
the total number of days, hours and minutes from, for example cell C
I am using Excel 2003 v11.
Message posted from http://www.ExcelForum.com
I'm drawing a blank on how to do this with a shorter
formula, but this should do:
=INT(SUM(C2:C31))&" days, "&INT((SUM(C2:C31)-INT(SUM
(C2:C31)))*24)&" hours, "&ROUND(((S...Format painting bars in a chart
I have a funky problem..not sure why. Let's say I create a bar chart, and
format each of the bars with a nice set of gradient colors, that varies based
upon the data series source. In other words, several different color bars,
on standard color per series.
Now, if I happen to "touch" the data behind the chart in any way (in my
case, a pivot table), the data element I change seems to go back to a
"default" color scheme. I still have other bars that look like I want, but
say, one month bar, for one data series, now looks different than the other
11 for the year...Index fields and formatting
I've inherited a document and some of the index fields - XE - are formatted
as bold and I want to remove the formatting.
I found a similar post going back to 2005 and the answer then was use
I have Word 2003 and 2007 and just wondering if this is still the case.
Also, is there a way to bold the text without bolding the index field.
Bolding the line with fields switched off still bolds the field.
To change the formatting applied to the XE (index entry) fields, you can
make use of Find and Replace. Display the Find and Repla...