<longre@wanadoo.fr>Date: Sun, 12 Sep 1999 14:49:22 +0200
From: Laurent longre 
Subject: Re: macro : colorier conditionnellement les cellules
Newsgroups: microsoft.public.fr.excel


Requête initiale
Soit une plage Ai:Gi, les cellules des lignes 1,3,5,7 etc.. peuvent contenir les donn&eacute;s suivantes : vide, 1,2,3,4,5 ou 6

Les cellules des lignes 2,4,6,8 etc.. doivent se colorer d'une couleur diff&eacute;rente selon le contenu de la cellule situ&eacute; juste au dessus.
=> rien si vide, rouge si 1, bleu si 2 etc..


R&eacute;ponse en macro
En prenant vide = aucune couleur, 1 = rouge, 2 = bleu et diverses autres
couleurs pour les valeurs 3 à 6, sur la plage A1:G7

dim I As long, J As Integer
dim Couleurs
Couleurs = array(xlColorIndexNone, 3, 5, 1, 2, 4, 6)
application.screenupdating = false
for I = 1 to 7 Step 2
  for J = 1 to 7
    cells(I + 1, J).Interior.ColorIndex = Couleurs(cells(I, J))
  next J
next I

Pour &eacute;largir le nombre de lignes, remplace le 7 de 'for I = 1 to 7'.
Pour sp&eacute;cifier d'autres couleurs, remplis le tableau array(...) avec les
codes de couleur que tu veux associer respectivement aux valeurs vide,
1, 2, 3, 4, 5 et 6. Tu trouveras les codes de couleur dans la page
d'aide de VBA sur la propri&eacute;t&eacute; ColorIndex.


******************************************************************


From: "Peter Beach"<pbeach@globalnet.co.uk> 
Subject: Re: How to make data changes visible ?
Date: Thu, 19 Aug 1999 06:12:33 +0100
Newsgroups: microsoft.public.excel.misc

It will make changed cells Italic, and will then make them unitalic when the book is saved.  You might prefer to move the code from BeforeSave to BeforeClose.
Ce code met en italique toutes les cellules modifi&eacute;s et les r&eacute;tablit en mode normal quand le classeur est sauv&eacute;.

Private Sub Workbook_BeforeSave(ByVal saveasUI As Boolean, Cancel As
Boolean)
  dim s As Worksheet
  dim OrigSheet As Worksheet

  set OrigSheet = activesheet
  for each s In ThisWorkbook.worksheets
    s.select
    s.Usedrange.Font.Italic = false
  next s
  OrigSheet.Activate
end Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Excel.range)
  Target.Font.Italic = true
end Sub


******************************************************************


From: "Yann Solo"
Newsgroups: microsoft.public.excel.programming
Subject: Re: Cell formatting
Date: Tue, 31 Aug 1999 00:39:13 +0300


each time selected cell change, A12 will be checked. if the value of A12
is the word "yes" then the range of cells will be yellow (Red Green Blue
code is 255,255,0)

Private Sub Worksheet_selectionChange(ByVal Target As Excel.range)

if range("A12") = "yes" then
    range("A1:A15").Interior.Color = RGB(255, 255, 0)
end if

end Sub


********************************************************************

From: Patrick Molloy
Newsgroups: microsoft.public.excel.misc
Subject: Re: Automatically color a cell when certain character is entered
Date: Thu, 14 Oct 1999 07:21:01 GMT


Requete initiale
I am wondering if it is possible to customize a cell so that if one types a certain character in a cell the color of that cell automatically turns into a certain predefined color for that character.

Reponse
you can use the sheets change event to see if the value in specific cell has changed, then reset the color. Right click on the sheet taband select View Code then paste in this

Private Sub Worksheet_Change(ByVal Target As Excel.range)
    if Target.address(false, false) = "A1" then
        Target.Interior.ColorIndex = Asc(Target) Mod 56
    end if
end Sub