Code to have cells in the row formatted "Italics" when combo boxvalue is set

[AA Arens]

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

 

[Shane Devenshire]

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

 

[CLR]

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
ActiveCell.Select
If ActiveCell.Column = 1 Then
If ActiveCell.Value = "EUR" Then
Selection.Offset(0, 1).Font.Italic = True
Selection.Offset(0, 2).Font.Italic = True
Selection.Offset(0, 3).Font.Italic = True
Else
Selection.Offset(0, 1).Font.Italic = False
Selection.Offset(0, 2).Font.Italic = False
Selection.Offset(0, 3).Font.Italic = False
End If
End If
End Sub

Vaya con Dios,
Chuck, CABGx3

 

[AA Arens]

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

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

 

[shanedevenshire]

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

 

[AA Arens]

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