colorier une cellule suite a une condition Résolu/Fermé

Question

Bonjour, 
Bonjour,

Voici mon code qui me permet de vérifier des champs qui doivent être remplis aussi la vérification d'autres conditions avant qu'il mette un xx dans la cellule 42 si toutes les conditions sont vraies 

j'aimerais bien , colorier les cellules qui sont vides après la vérification, j'ai essayé le code ce dessus mais il marche pas vraiment 

est ce que vous pourrirez m'aider à trouver mon erreur 

merci 


  Dim c As Integer
         Dim q As Integer
  Dim MaPlage As Range
            
       For q = 2 To ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
    Set MaPlage = Range("A:H, J:R").Rows(q)
 For Each c In MaPlage
If IsEmpty(c) Then c.Interior.ColorIndex = 3

If CStr(ActiveSheet.Cells(q, 31).Value) = "Completed - Appointment made / Complété - Nomination faite" _
    And WorksheetFunction.CountIf(MaPlage, "") = 0 Then
        Select Case UCase(ActiveSheet.Cells(q, 14).Value)
            Case "INA_CIN"
              ActiveSheet.Cells(q, 42).Value = "XX"
         End Select
      End If

Next c
Next q
    


Configuration: Windows 7 / Chrome 44.0.2403.130

cs_Le Pivert · Answer

Bonjour,

voir ici:

https://forums.commentcamarche.net/forum/affich-32709605-colorier-une-cellule#top

r4944 · Answer

salut j'ai reussi a le faire tourner mais le probleme qu'il prend pas toujours en consideration la derniere ligne du tableau c'est bizarre un peu 
voici mon code 
     Dim g As Integer
         Dim q As Integer
        
             Dim c As Range
   Dim d As Range
  Dim MaPlage As Range
            
       For q = 2 To ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
    
    Set MaPlage = Range("A:H, J:R").Rows(q)
     For Each c In MaPlage.Cells
    If Len(c.Value) = 0 Then c.Interior.Color = vbYellow
    
    If CStr(ActiveSheet.Cells(q, 31).Value) = "Completed - Appointment made / Complété - Nomination faite" _
         And WorksheetFunction.CountIf(MaPlage, "") = 0 Then
        Select Case UCase(ActiveSheet.Cells(q, 14).Value)
            Case "INA_CIN"
                ActiveSheet.Cells(q, 42).Value = "XX"
        End Select
    End If
    
    Next c
Next q

cs_Le Pivert · Answer

Dim g As Integer
Dim q As Integer
Dim Col As Range
Dim c As Range
Dim d As Range
Dim MaPlage As Range
          
       For q = 2 To Range("R65536").End(xlUp).Row 'dernière ligne, a adapter à la colonne la plus remplie ici la R
    
   ' Set MaPlage = Range("A:H, J:R").Rows(q)
     For Each Col In Range("A1:A" & q & ",H1:H" & q & ",J1:J" & q & ",R1:r" & q & "").Columns ' a adapter
     For Each c In Col.Cells
    If Len(c.Value) = 0 Then c.Interior.Color = vbYellow
    
    'If CStr(ActiveSheet.Cells(q, 31).Value) = "Completed - Appointment made / Complété - Nomination faite" _
       '  And WorksheetFunction.CountIf(MaPlage, "") = 0 Then
       ' Select Case UCase(ActiveSheet.Cells(q, 14).Value)
          '  Case "INA_CIN"
             '   ActiveSheet.Cells(q, 42).Value = "XX"
        'End Select
   ' End If
    
    Next
Next
Next

cs_Le Pivert · Answer

Si tu veux aller à la dernière ligne de tes colonnes:

Dim q, q1, q2, q3 As Integer
Dim Col As Range
Dim c As Range
Dim MaPlage As Range
  q = Range("A65536").End(xlUp).Row
  q1 = Range("H65536").End(xlUp).Row
  q2 = Range("J65536").End(xlUp).Row
  q3 = Range("R65536").End(xlUp).Row
  
   Set MaPlage = Range("A1:A" & q & ",H1:H" & q1 & ",J1:J" & q2 & ",R1:R" & q3 & "").Columns

For q = 2 To ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
       
For Each Col In MaPlage
     For Each c In Col.Cells
    If Len(c.Value) = 0 Then c.Interior.Color = vbYellow
    
    'If CStr(ActiveSheet.Cells(q, 31).Value) = "Completed - Appointment made / Complété - Nomination faite" _
       '  And WorksheetFunction.CountIf(MaPlage, "") = 0 Then
       ' Select Case UCase(ActiveSheet.Cells(q, 14).Value)
          '  Case "INA_CIN"
             '   ActiveSheet.Cells(q, 42).Value = "XX"
        'End Select
   ' End If
    
    Next c
Next Col
Next q

Colorier une cellule suite a une condition

4 réponses

Discussions similaires