VBA: Conditional Formatting one column based on another

[jazzhayward]

Hi All,

I have numerical values in column K and a currency text in column O (NZD, USD, AUD etc). I've included a dummy table below.

I want to have the numerical value in column K (Fee) formatted to a red font colour if the text in column O (Invoice Currency) on the same row is not NZD. The data has a varying number of rows.

I've tried the below code (see Column K format), but at the moment it's formatting all the values in column K to a red font colour. I'm assuming it has something to do with the formula in the conditional formatting statement. I thought using $O3 would mean it cycles down but I'm assuming I'll need to set a similar range.

I do have some code written for formatting Column O for a different set of parameters (see Column O format). This ideally will sit before the formatting for column K. I'm not sure if any of the code is useful for the column K formatting but thought I would paste it regardless. Both of these sections will be part of a larger code.

Can you please let me know where I'm going wrong?

Thanks in advance

Column K Format

Sub TestingTextFormatting2()

Dim LRKcol As Long

    LRKcol = Cells(Rows.Count, "K").End(xlUp).Row

Dim rngrng As Range
Dim rngCell As Range
Set rngrng = Range("K3:K" & LRKcol)

For Each rngCell In rngrng
With rngCell
.FormatConditions.Add Type:=xlExpression, Formula1:=" =$O3<>""NZD"" "
With .Font
.Color = vbRed
End With
End With

Next rngCell

End Sub

Column O Format

Sub TestingTextFormatting()

Dim LROcol As Long
    
    LROcol = Cells(Rows.Count, "O").End(xlUp).Row

With Range("O3:O" & LROcol).FormatConditions.Add(xlTextString, TextOperator:=xlDoesNotContain, String:="NZD")
With .Font
.Color = vbRed
End With
End With

End Sub

Customer

Program Name

Revenue Office

Sales Office

Delivery Office

Delivery Office Region

Start Date

Client Name

Client Name

Unbilled Fees

Fee

Primary AE

Last Billing Status Date

Last Updated By

Invoice Currency

ABC

Name1

The Moon

New York

Auckland

New Zealand

13/05/2024​

Destinee Brooks

Destinee Brooks

2250​

2250​

Asher Ross

13/05/2024​

Mouse, Mickey

NZD

DEF

Name2

The Moon

New York

Auckland

New Zealand

22/05/2024​

Malakai Mata

Malakai Mata

1450​

1450​

Karly Caldwell

24/05/2024​

Mouse, Mickey

NZD

GHI

Name3

The Moon

New York

Auckland

New Zealand

13/03/2024​

Alonso Douglas

Alonso Douglas

1950​

1950​

Marie Kent

27/05/2024​

Mouse, Mickey

NZD

ABC1

Name4

The Moon

New York

Auckland

New Zealand

20/05/2024​

Bennett Jacobs

Bennett Jacobs

7000​

7000​

Gaven Salazar

24/05/2024​

Mouse, Mickey

NZD

DEF1

Name5

The Moon

New York

Auckland

New Zealand

18/04/2024​

Javier Phelps

Javier Phelps

2500​

2500​

Erin Wiggins

01/05/2024​

Mouse, Mickey

NZD

GHI1

Name6

The Moon

New York

Auckland

New Zealand

20/05/2024​

Annalise Henderson

Annalise Henderson

2552​

2552​

Cale Chase

24/05/2024​

Mouse, Mickey

NZD

ABC11

Name7

The Moon

New York

Auckland

New Zealand

20/05/2024​

Meadow Pollard

Meadow Pollard

989.38​

989.38​

Konner Joseph

24/05/2024​

Mouse, Mickey

NZD

DEF11

Name8

The Moon

New York

Auckland

New Zealand

22/05/2024​

Melvin Cabrera

Melvin Cabrera

3045​

3045​

Trenton Glover

24/05/2024​

Mouse, Mickey

NZD

GHI11

Name9

The Moon

New York

Auckland

New Zealand

02/05/2024​

Jaslene Saunders

Jaslene Saunders

800​

800​

Reid Benitez

15/05/2024​

Mouse, Mickey

USD

ABC111

Name10

The Moon

New York

Auckland

New Zealand

07/05/2024​

Simon Gibbs

Simon Gibbs

800​

800​

Jazmin English

15/05/2024​

Mouse, Mickey

USD

DEF111

Name11

The Moon

New York

Auckland

New Zealand

20/05/2024​

Karley Mejia

Karley Mejia

800​

800​

Zachariah Hall

24/05/2024​

Mouse, Mickey

USD

GHI111

Name12

The Moon

New York

Auckland

New Zealand

07/05/2024​

Tamara Taylor

Tamara Taylor

800​

800​

Cadence Rogers

15/05/2024​

Mouse, Mickey

USD

ABC1111

Name13

The Moon

New York

Auckland

New Zealand

02/05/2024​

Ronan Downs

Ronan Downs

800​

800​

Kaylah Pham

15/05/2024​

Mouse, Mickey

USD

DEF1111

Name14

The Moon

New York

Auckland

New Zealand

03/05/2024​

Jairo Cardenas

Jairo Cardenas

800​

800​

Linda Young

15/05/2024​

Mouse, Mickey

USD

GHI1111

Name15

The Moon

New York

Auckland

New Zealand

06/05/2024​

Jaylin Patterson

Jaylin Patterson

800​

800​

Camryn Ali

15/05/2024​

Mouse, Mickey

USD

 

[myall_blues]

Because it's difficult to get the quotes right I always find it easier to build the format expression in a string and then use the string as the formula. You can do the whole range at once - you don't need to go cell by cell.

Sub TestingTextFormatting2()
    Dim LRKcol As Long
    Dim formatCon As String
    Dim rngrng As Range
    LRKcol = Cells(Rows.Count, "K").End(xlUp).Row
    Set rngrng = Range("K3:K" & LRKcol)
    formatCon = "=$O3<>" & """NZD"""
    With rngrng
        .FormatConditions.Delete
        .FormatConditions.Add Type:=xlExpression, Formula1:=formatCon
        With .FormatConditions(1).Interior
            .Color = vbRed
        End With
    End With
End Sub

 

[jazzhayward]

Thanks for your help! I had seen solutions where the formula was built as a string but I couldn't get it to work haha.