Format cells based on other cell's value

  • Thread starter Thread starter SandroK
  • Start date Start date
S

SandroK

Hi there!
If you can help me with conditional formatting. 've tried to find
someting like this before posting, but unsuccessfully.

In A1 cell I have list (Data-Validation-list) of 2 values "GEL" and
"USD".

I have to format several rages (C3:C20, D3:D20 ... users are entering
data there.) based on the value of A1 cell.
If A1="USD" then
these ranges should be formated as "$ ###,#0.00"
else (A1="GEL")
these ranges should be formated as "GEL ###,#0.00"

(No need to have '$' and 'GEL' in red, just to bring your attention).
VBA is only solution?

Thanks a lot!
SandroK
 
Sandro,

VBA I am afraid, Conditional Formatting doesn't set number formats. This
code will do it for the 2 examples you provide, you will need to add other
currencies

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = True
On Error GoTo ws_exit
If Not Intersect(Target, Range("A1")) Is Nothing Then
Select Case Target.Value
Case "USD": Range("B1:K10").NumberFormat = "$ ###,#0.00"
Case "GEL": Range("B1:K10").NumberFormat = "GEL ###,#0.00"
End Select
End If

ws_exit:

Application.EnableEvents = True
End Sub

This is worksheet event code, so it goes into the worksheet code module.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top