Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Dec 2009
    Location
    Palm Coast, Florida
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need help with conditional formatting using .net

    I would like to apply conditional formatting to a defined range in Excel 2010. I want to apply a cell interior color if the cell contains blanks. I have recorded a macro as a guide but I am unable to translate the macro into .Net.

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Can you provide your existing .Net code (VB, C#, other?) as well as the recorded macro?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    New Lounger
    Join Date
    Dec 2009
    Location
    Palm Coast, Florida
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This portion of code that I am using to apply the conditional formatting.

    Code:
            Imports Microsoft.Office.Interop.Excel
            Imports System.Runtime.InteropServices
            Imports Microsoft.Office.Interop
    
         Public Sub format()
            Dim xlApp As New Excel.Application
            Dim xlWorkBook As Excel.Workbook
            Dim xlWorkSheet As Excel.Worksheet
            Dim xlDestRange As Excel.Range
            Dim m As String
            Dim n As Long
    
            If CheckBox1.Checked = True Then
                '--> Opens Destination Workbook.
                xlWorkBook = xlApp.Workbooks.Open(strPath & "Worksheet.xlsx")
    
                '--> Display Excel         
                xlApp.Visible = True
    
                '--> Set the first destination worksheet         
                xlWorkSheet = xlWorkBook.Worksheets(1)
                n = xlWorkSheet.Range("C" & xlWorkSheet.Rows.Count).End(XlDirection.xlUp).Row
                xlDestRange = xlWorkSheet.Range("C1:C" & n)            
            End If
    
           This is where I would be inserting the code for conditional formatting.
    
        End Sub
    This is the recorded macro:
    Code:
    Sub Macro1()
    '
    ' Macro1 Macro
    
        Range("A1:A11").Select
        Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=LEN(TRIM(A1))=0"
        Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
        With Selection.FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = -0.249946592608417
        End With
        Selection.FormatConditions(1).StopIfTrue = False
    End Sub

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    That should translate to:
    Code:
                xlDestRange = xlWorkSheet.Range("C1:C" & n)
                xlDestRange.Select()
                xlDestRange.FormatConditions.Add(Type:=XlFormatConditionType.xlExpression, Formula1:="=LEN(TRIM(A1))=0")
                xlDestRange.FormatConditions(xlDestRange.FormatConditions.Count).SetFirstPriority()
                With xlDestRange.FormatConditions(1).Interior
                    .PatternColorIndex = XlColorIndex.xlColorIndexAutomatic
                    .ThemeColor = XlThemeColor.xlThemeColorDark1
                    .TintAndShade = -0.249946592608417
                End With
                xlDestRange.FormatConditions(1).StopIfTrue = False
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    New Lounger
    Join Date
    Dec 2009
    Location
    Palm Coast, Florida
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Many thanks, Rory. I can see now where I found myself stumbling over the "xlFormatConditionType." I don't know why I failed to see that. With very little tweaking to fit your code into mine it worked perfectly. Not at first though what I failed to realize that the worksheet that I needed to apply the conditional formatting to needed to be selected....My workbook has two worksheets and the second one was selected by previous actions which caused an error when I ran the line xlDestRange.Select(). I'm okay with that because it gave me an opportunity to continue learning how to troubleshoot my own code. My hat's off to you Rory thank you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •