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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

AA Arens - 03 Feb 2008 14:38 GMT

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

Reply to this Message

Shane Devenshire - 03 Feb 2008 22:33 GMT

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

> Hi,
>
[quoted text clipped - 14 lines]
> Bart
> Excel 2003

Reply to this Message

AA Arens - 05 Feb 2008 04:16 GMT

On Feb 4, 5:33 am, "Shane Devenshire" <shanedevensh...@sbcglobal.net>
wrote:

> Hi,
>
[quoted text clipped - 22 lines]
> > 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

Reply to this Message

shanedevenshire@sbcglobal.net - 09 Feb 2008 15:27 GMT

> On Feb 4, 5:33 am, "Shane Devenshire" <shanedevensh...@sbcglobal.net>
> wrote:
[quoted text clipped - 42 lines]
>
> - 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

Reply to this Message

AA Arens - 10 Feb 2008 12:52 GMT

On Feb 9, 10:27 pm, shanedevensh...@sbcglobal.net wrote:

> On Feb 4, 8:16 pm,AAArens<bartvandon...@gmail.com> wrote:
>
[quoted text clipped - 72 lines]
> 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

Reply to this Message

CLR - 04 Feb 2008 12:40 GMT

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

> Hi,
>
[quoted text clipped - 14 lines]
> Bart
> Excel 2003

Reply to this Message