Excel VBA Selecting Multiple columns with last row and apply Condition Format

0 votes

Please assist me with the following question. I'm trying to apply Condition Format to many columns with the final row selected, but it's not working the way I want it to.

Working One:

Dim lr As Long

With Range("E:E,F:F,H:H")
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=0"
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    .FormatConditions(1).Interior.Color = 13561798
    .FormatConditions(1).StopIfTrue = False
End With

This one is not working :

Dim lr As Long

    lr = Cells(Rows.Count, 1).End(xlUp).Row

With Range(""E2:E" & lr,"F2:F" & lr,"H2:H" & lr)
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=0"
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    .FormatConditions(1).Interior.Color = 13561798
    .FormatConditions(1).StopIfTrue = False
End With

And I'm trying to apply color to a cell that contains error "#N/A" but it is not working.

Dim lr As Long

    lr = Cells(Rows.Count, 1).End(xlUp).Row

With Range(""E2:E" & lr,"F2:F" & lr,"H2:H" & lr)
    .FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, Formula1:="=#N/A"
    .FormatConditions(.FormatConditions.Count).SetFirstPriority
    .FormatConditions(1).Interior.Color = 13561798
    .FormatConditions(1).StopIfTrue = False
End With

Any help with one above 2 queries would be appreciated.

Mar 21, 2023 in Others by narikkadan
• 63,720 points
841 views

1 answer to this question.

0 votes

Try this:

Sub Test()
    Dim lr As Long, ws As Worksheet
    
    Set ws = ActiveSheet 'for example
    lr = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    With ws.Range("E2:E" & lr & ",F2:F" & lr & ",H2:H" & lr)
        With .FormatConditions.Add(Type:=xlCellValue, _
                                   Operator:=xlEqual, Formula1:="=0")
            .SetFirstPriority
            .Interior.Color = 13561798
            .StopIfTrue = False
        End With
    End With
End Sub
answered Mar 21, 2023 by Kithuzzz
• 38,010 points

Related Questions In Others

0 votes
1 answer

How to Freeze Top Row and Apply Filter in Excel Automation with C#

Try this: // Fix first row workSheet.Activate(); workSheet.Application.ActiveWindow.SplitRow = 1; workSheet.Application.ActiveWindow.FreezePanes ...READ MORE

answered Oct 22, 2022 in Others by narikkadan
• 63,720 points
2,337 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,670 points
820 views
0 votes
1 answer

Create unique rows in Excel with limited data to be used in multiple columns

This setup isn't readily generalizable, though since ...READ MORE

answered Oct 14, 2022 in Others by narikkadan
• 63,720 points
605 views
0 votes
1 answer

How to find out how many rows and columns to read from an Excel file with PHPExcel?

Solution: $file_name = htmlentities($_POST['file_name']); $sheet_name = htmlentities($_POST['sheet_name']); $number_of_columns = htmlentities($_POST['number_of_columns']); $number_of_rows ...READ MORE

answered Oct 23, 2022 in Others by narikkadan
• 63,720 points
7,381 views
0 votes
1 answer

Format an Excel Sheet in Python

The below code will help you refer ...READ MORE

answered Nov 6, 2022 in Others by narikkadan
• 63,720 points
1,223 views
0 votes
1 answer

Change sheet tab color depending on CountA function result

Try using the ws. prefix to set ...READ MORE

answered Mar 28, 2023 in Others by Kithuzzz
• 38,010 points
218 views
0 votes
1 answer

Conditional formatting based on value with offset

Apart than the altered cell, Conditional Formatting ...READ MORE

answered Apr 4, 2023 in Others by Kithuzzz
• 38,010 points
461 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
996 views
0 votes
1 answer

How to use COUNTIFS with multiple conditions and columns in Excel?

Use  SUMPRODUCT and double unay operators: =SUMPRODUCT ...READ MORE

answered Apr 4, 2023 in Others by Kithuzzz
• 38,010 points
412 views
0 votes
1 answer

Excel VBA: Obtain the Column and Row of current element in "For Each" loop

Try this: MsgBox rng.Address(RowAbsolute:=False, ColumnAbsolute:=F ...READ MORE

answered Feb 14, 2023 in Others by Kithuzzz
• 38,010 points
942 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP