none
Color background on a condition RRS feed

  • Question

  • If I want to color a range of cells starting with a cell which has "q" in it, to its right 3 cells in the same row.

    I want to color those range only if the first cell has "q" in it.

    Why this code is only changing the color of selection?

    Dim sht As Worksheet
        Dim cel As Range
        For Each sht In ActiveWorkbook.Worksheets
            For Each cel In sht.UsedRange
              
    If cel.value = "q" Then Range(Selection, Selection.End(xlToRight)).Select
       With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorDark2
            .TintAndShade = -0.249977111117893
            .PatternTintAndShade = 0
        End With
    
        Next cel
        Next sht
    End Sub


    • Edited by Vbaer Sunday, May 13, 2012 8:11 AM
    Sunday, May 13, 2012 8:10 AM

Answers

  • How about:

    Sub qwerty()
    Dim sht As Worksheet
    Dim cel As Range
    For Each sht In ActiveWorkbook.Worksheets
            For Each cel In sht.UsedRange
            If cel.Value = "q" Then
                cel.Resize(1, 4).Select
                With Selection.Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .ThemeColor = xlThemeColorDark2
                    .TintAndShade = -0.249977111117893
                    .PatternTintAndShade = 0
                End With
            End If
            Next cel
    Next sht
    End Sub

    So if A1 has the "q", then A1,B1,C1,D1 will be shaded.


    gsnu201202

    • Marked as answer by Vbaer Sunday, May 13, 2012 11:45 AM
    Sunday, May 13, 2012 10:55 AM
    Moderator
  • hi Shripadbhat,
     
    [code]
    Sub test()
    Dim sht As Worksheet
    Dim cel As Range
    For Each sht In ActiveWorkbook.Worksheets
      For Each cel In sht.UsedRange
          If cel.Value = "q" Then
            With cel.Resize(1, 3).Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorDark2
            .TintAndShade = -0.249977111117893
            .PatternTintAndShade = 0
            End With
          End If
      Next cel
    Next sht
    End Sub
    [/code]
     
    --
    isabelle
     
    Le 2012-05-13 04:10, Shripadbhat a écrit :
    > If I want to color a range of cells starting with a cell which has "q" in it, to its right 3 cells in the same row.
    >
    > I want to color those range only if the first cell has "q" in it.
    >
    > Why this code is only changing the color of selection?
    >
    > Dim sht As Worksheet
    >      Dim cel As Range
    >      For Each sht In ActiveWorkbook.Worksheets
    >          For Each cel In sht.UsedRange
    >
    > If cel.value ="q"  Then Range(Selection, Selection.End(xlToRight)).Select
    >     With Selection.Interior
    >          .Pattern = xlSolid
    >          .PatternColorIndex = xlAutomatic
    >          .ThemeColor = xlThemeColorDark2
    >          .TintAndShade = -0.249977111117893
    >          .PatternTintAndShade = 0
    >      End With
    >
    >      Next cel
    >      Next sht
    > End Sub
    >
    >
    >
     
    • Marked as answer by Vbaer Sunday, May 13, 2012 11:45 AM
    Sunday, May 13, 2012 10:57 AM

All replies

  • How about:

    Sub qwerty()
    Dim sht As Worksheet
    Dim cel As Range
    For Each sht In ActiveWorkbook.Worksheets
            For Each cel In sht.UsedRange
            If cel.Value = "q" Then
                cel.Resize(1, 4).Select
                With Selection.Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .ThemeColor = xlThemeColorDark2
                    .TintAndShade = -0.249977111117893
                    .PatternTintAndShade = 0
                End With
            End If
            Next cel
    Next sht
    End Sub

    So if A1 has the "q", then A1,B1,C1,D1 will be shaded.


    gsnu201202

    • Marked as answer by Vbaer Sunday, May 13, 2012 11:45 AM
    Sunday, May 13, 2012 10:55 AM
    Moderator
  • hi Shripadbhat,
     
    [code]
    Sub test()
    Dim sht As Worksheet
    Dim cel As Range
    For Each sht In ActiveWorkbook.Worksheets
      For Each cel In sht.UsedRange
          If cel.Value = "q" Then
            With cel.Resize(1, 3).Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorDark2
            .TintAndShade = -0.249977111117893
            .PatternTintAndShade = 0
            End With
          End If
      Next cel
    Next sht
    End Sub
    [/code]
     
    --
    isabelle
     
    Le 2012-05-13 04:10, Shripadbhat a écrit :
    > If I want to color a range of cells starting with a cell which has "q" in it, to its right 3 cells in the same row.
    >
    > I want to color those range only if the first cell has "q" in it.
    >
    > Why this code is only changing the color of selection?
    >
    > Dim sht As Worksheet
    >      Dim cel As Range
    >      For Each sht In ActiveWorkbook.Worksheets
    >          For Each cel In sht.UsedRange
    >
    > If cel.value ="q"  Then Range(Selection, Selection.End(xlToRight)).Select
    >     With Selection.Interior
    >          .Pattern = xlSolid
    >          .PatternColorIndex = xlAutomatic
    >          .ThemeColor = xlThemeColorDark2
    >          .TintAndShade = -0.249977111117893
    >          .PatternTintAndShade = 0
    >      End With
    >
    >      Next cel
    >      Next sht
    > End Sub
    >
    >
    >
     
    • Marked as answer by Vbaer Sunday, May 13, 2012 11:45 AM
    Sunday, May 13, 2012 10:57 AM
  • Thank you so much isabelleV and Gary's Student.

    You are perfect in solving.!


    • Edited by Vbaer Sunday, May 13, 2012 12:03 PM
    Sunday, May 13, 2012 12:03 PM