On Feb 9, 10:27 pm, shanedevensh...@sbcglobal.net wrote:
> On Feb 4, 8:16 pm,AAArens<bartvandon...@gmail.com> wrote:
>
>
>
> > On Feb 4, 5:33 am, "Shane Devenshire" <shanedevensh...@sbcglobal.net>
> > wrote:
>
> > > Hi,
>
> > > Why not avoid code and just use Conditional Formatting? I haven't tried it
> > > with a combo box but it works fine with a Data Validation drop down list.
>
> > > Cheers,
> > > Shane
>
> > > "AAArens" <bartvandon...@gmail.com> wrote in message
>
> > >news:7ac672dc-0c43-4ebb-b1db-(E-Mail Removed)...
>
> > > > Hi,
>
> > > > Anybody know the VB code to have cells in the row formatted "Italics"
> > > > when a combo choice value "EUR" left of the cells is set?
>
> > > > Column
> > > > A B
> > > > C D
> > > > value = EUR (format italics) (<- same) (<- same)
> > > > USD (format normal) (<- same) (<- same)
> > > > EUR (format italics) (<- same) (<-
> > > > same)
>
> > > > I cannot use Conditional Formatting as the conditions are already
> > > > used.
>
> > > > Bart
> > > > Excel 2003
>
> > 1)
> > For column A, I use data validation (list) to choose currency. Is it
> > possible to use data validation in the cells in column B and onwards
> > and to get it Italics formatted?
>
> > 2)
> > I already use the first two conditions of CF, if it works for the
> > third condition, what is the code to get the format italics?
>
> > Bart- Hide quoted text -
>
> > - Show quoted text -
>
> Hi,
>
> Suppose your Data Validation entries are in cell A2:A10 and you want
> to Format B2:F10 to italic when and entry is picked from the list drop
> down in column A:
>
> 1. Select the range B2:F10 and choose Format, Conditional
> Formatting,
> 2. From the first dropdown pick Formula is
> 3. In the next box type =$A2="EUR"
> 4. Click the Format button and select the Font tab, and pick Italic
> under Font Style
> 5. Click OK twice.
>
> In step 3 the $ before the A is critical.
>
> Side comment, I don't know what your comment "I cannot use Conditional
> Formatting as the conditions are already used" means.
> But if you are using Excel 2003 the maximum number of conditions for a
> single cell are 3, in 2007 that number is unlimited. If you are
> already using three conditions in each of these columns it might still
> be possible to do what you need without VBA, but we would need to know
> what those other conditions are. I can give you VBA to do this but
> first let's make sure you really need it.
>
> Cheers,
> Shane
He Shane,
You formula works if I drop it as first condition and move the
existing two as 2nd and 3rd. If I drop your formula in the 3rd it
doesn't work as one of the first conditions already applies.
The reason why two conditions are used is that I want to have the
cells either filled pattern green or orange, this depends on whether a
cell in another column is empty or not PLUS italic of not, depend on
chosen currency.
Cell in column G is filled in or not -> data filled cells in range H,
I, etc. need to be orange or green (G = data or no data)
Then:
Column F -> Currency is USD or EUR -> Data that is meanwhile either
orange or green, need to be also italics IF F = EUR.
So, Cells column H, I, etc. can be:
empty (white, nothing happened)
filled then pattern orange
filled then pattern green
filled then pattern + orange iitalics if EUR price
filled then pattern + green italics if EUR price
All dependencies are horizontally, so
F1 - G1 - H1 - .....Z1
F2 - G2 - H2 - .....Z2
These are the first two conditions (I start at row 9):
CF1: =AND(ISNUMBER(H9),LEFT(CELL("format",$G9),1)="D",--
RIGHT(CELL("format",$G9),1)<6,LEN(CELL("format",$G9))=2,LEN($G9)>0)
CF2: =ISNUMBER(H9)
Bart
|
Similar Threads
