View original (French)

VBA If cell color then search for a value in a cell

Solved
jambontomate Posted messages 44 Status Member -  
jambontomate Posted messages 44 Status Member -
Hello everyone,

I have cells with colors (for example: B2:B10 are yellow). The idea is:

If B2 is yellow, then B3 = "value of cell F19 from sheet XXXX".

How can I write it in VBA, or if a formula is sufficient (even better).

Thank you very much.

3 answers

  1.
    g Posted messages 1285 Status Member 578
     
    Hello,

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Range("B2").Interior.ColorIndex = 6 Then
    Range("B3") = Sheets("Sheet2").Range("F19")
    End If
    End Sub

    Best regards
    1
    1. jambontomate Posted messages 44 Status Member 1
       
      Thank you, I’ll make some changes and see if everything is good, thanks!
      0
    2. jambontomate Posted messages 44 Status Member 1
       
      One note: If this idea applies to a range of cells, e.g., range B2:B20, if B2:B10 = yellow then C2:C10 = "value from sheet "xxx" of cell "Y" if B10:B20 = red then C10:C20 = "value from sheet "xxx" of cell "Y".

      Does the VBA formula change radically or just putting range(B2:B20)...? Thank you.
      0
    3. g Posted messages 1285 Status Member 578
       
      The code changes, but in order to establish it, precise data is needed.
      Names of sheets, designation of command cells, designation of result cells, etc....
      You may even attach a filled-out file.
      0
    4. g Posted messages 1285 Status Member 578
       
      Otherwise, you can check this code for the return values in Sheet2 F19 and F20

      Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      For i = 2 To 10
      If Range("B" & i).Interior.ColorIndex = 6 Then
      Range("C" & i) = Sheets("Sheet2").Range("F19")
      ElseIf Range("B" & i).Interior.ColorIndex <> 6 Then
      Range("C" & i) = ""
      End If
      Next i
      For j = 11 To 20
      If Range("B" & j).Interior.ColorIndex = 3 Then
      Range("C" & j) = Sheets("Sheet2").Range("F20")
      ElseIf Range("B" & j).Interior.ColorIndex <> 3 Then
      Range("C" & j) = ""
      End If
      Next j
      End Sub
      0
    5. jambontomate Posted messages 44 Status Member 1
       
      I will attach a file this evening because for now the workplace security is blocking. In fact, it will be more complicated for the macro.

      I will have a column B2:B100 containing four fixed colors (blue, yellow, orange, red) that can change regarding the ranges of cells in this column often.

      I would like the macro, if possible, to retrieve a different value for the cells C2:C100 for each color change (in this case: four different values = four different colors)

      For example: for the yellow color, the value from cell B19 of sheet 1, for the blue color, the value from cell B19 of sheet 2, etc.

      The difficulty with this macro is that the colored cells can change their cell range; one day B2:B10 might be yellow, and the next day B4:B15 might be blue, etc.

      Is this already possible? If so, I will send you a file this evening with an example if it’s not clear.
      0
  2. Mike-31 Posted messages 18205 Registration date   Status Contributor Last intervention   5 147
     
    Hello,

    How are your cells colored, manually or with conditional formatting?
    If it's conditional formatting, what type of formatting do you use?

    --
    A+
    Mike-31

    A period of failure is a perfect time to sow the seeds of knowledge.
    0
    1. jambontomate Posted messages 44 Status Member 1
       
      The initial cells are set manually. Then, through VBA, the other cells automatically take the color of the initial cells.
      0
  3. jambontomate Posted messages 44 Status Member 1
     
    A remark: If this idea applies to a range of cells, e.g., range B2:B20, if B2:B10 = yellow then C2:C10 = "value from sheet 'xxx' in cell 'Y'; if B10:B20 = red then C10:C20 = "value from sheet 'xxx' in cell 'Y'.

    Does the VBA formula change radically or do you just put range(B2:B20)...? Thank you.
    0