Format cells in column H based on text content in column C

D

Declamatory

Active Member
Joined
Nov 6, 2014
Messages
319
Good Day Everyone,

Is someone able to show me how to format cells in column H based on the text content in column C.

If part of the text in column C5 contains the text “GBP” then the format of H5 should be "$#,##0.00_);[Red]($#,##0.00)" (GBP currency format)

If part of the text in column C5 contains the text “USD” then the format of H5 should be "[$$-409]#,##0.00" (USD currency format)

The same rule should apply for all cells in column H. There will be some blank rows in the data. It doesn't matter how these cells are formatted as there is nothing in them.

I have tried using conditional formatting and this works fine when recording the macro but errors when running the macro. Is there another routine using VBA that would work.

Thanks

Dec
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
Because this forms part of a larger macro that automates reporting. The conditional format painter doesn't work in VBA (I have posted a few threads trying to get a solution to this to no avail). I'm looking for either a solution to the VBA conditional format painter problem or a different method to achieve what I need using vba. Thanks.
 
Upvote 0
something along the lines of

If Range("c5") = "GBP" Then
Range("H5").Select
Selection.NumberFormat = "£#,##0_);[Red]($#,##0)"
End If
If Range("c5") = "USD" Then
Range("H5").Select
Selection.NumberFormat = "$ #,##0_);[Red]($#,##0)"
End If
 
Upvote 0
Thanks for posting Steve. Cell C5 may not ="GBP". The text in C5 may say "GBP Total" or "GBP Deals". Is there a way to do it for the whole column apart from repeating the script for each row. Thanks Dec
 
Upvote 0
dim c as range, rng
set rng=range("c5:c100")
for each c in rng
if c.value contains "GBP"

etc
 
Upvote 0
Sorry Steve, I just can't get this to even start. I'm a newbie to VBA so please forgive my ignorance but the VBA application doesn't like If c.value contains "GBP"
 
Upvote 0
Perhaps this solves your problem. Assumes 1st datarow is row 5 and there are no gaps in column c.
Code: 
Sub x()
introw = 5
 Do Until Range("C" & introw) = ""
    celltxt = Range("C" & introw).Text
    If InStr(1, UCase(celltxt), "GBP") Then
        Range("H" & introw).NumberFormat = "£#,##0_);[Red](£#,##0)"
    ElseIf InStr(1, UCase(celltxt), "USD") Then
        Range("H" & introw).NumberFormat = "[$$-409]#,##0_);[Red]($#,##0)"
    End If
    introw = introw + 1
 Loop
 
End Sub
 
Upvote 0
Hi Rob, I really appreciate your help. This works well apart from the fact that there are some blank rows in the data. Is it possible to change the formula to say look at every row up to row 500 and where C is a blank ignore formatting? There shouldn't be an occasion where there are more than 500 rows of data. Thanks again.
 
Upvote 0
adjusted
Code: 
Sub y()
     With ActiveSheet
        lLastRow = Get_Last_Row(.Cells)
    End With
 For introw = 5 To lLastRow
    celltxt = Range("C" & introw).Text
    If InStr(1, UCase(celltxt), "GBP") Then
        Range("H" & introw).NumberFormat = "£#,##0_);[Red](£#,##0)"
    ElseIf InStr(1, UCase(celltxt), "USD") Then
        Range("H" & introw).NumberFormat = "[$$-409]#,##0_);[Red]($#,##0)"
    End If
    introw = introw + 1
 Next
 
End Sub
Public Function Get_Last_Row(ByVal rngToCheck As Range) As Long
    Dim rngLast As Range
    
    Set rngLast = rngToCheck.Find(what:="*", searchorder:=xlByRows, searchdirection:=xlPrevious)
    
    If rngLast Is Nothing Then
        Get_Last_Row = rngToCheck.Row
    Else
        Get_Last_Row = rngLast.Row
    End If
    
End Function
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Solved
Stock history formula how to convert GBp to GBP
Replies
2
Views
166
vampsthevampyre
V
S
  • Solved
Sum column based on unique values in another column
2
Replies
11
Views
423
samide2001
S
A
  • Solved
Exclude columns in listbox on userform except first two columns & last column
2
Replies
16
Views
331
abdelfattah
A
E
  • Question
Customized text wrapping to new row (not within cell)
Replies
8
Views
180
rlv01
R
C
  • Solved
Text Join Function - Maintain date formats for multiple columns
Replies
3
Views
448
RoryA
RoryA

Forum statistics

Threads
1,214,590
Messages
6,120,423
Members
448,961
Latest member
nzskater

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top