# populate values  in adjacent  cells  based on duplicates name & font color



## Abdo (Sep 23, 2022)

Hello

I  want  if  the  values  in  column H  is  highlighted  by  green  font color ,  then  should  show  in  correspond cells in  column A is  0  and  if  the  value  in  column H is  black , then  should populate  in  correspond cells in  column A  is  the  same  value  and  if  the  column  C  contains duplicated  names  and  the   the  adjacent cells  in columns D,E are  empty  , then  should  fill  empty adjacent  cells  as  is existed   from first  time.
before
TEST (3).xlsxABCDEFGHI2AMOUNTCOUNTNAMEINSUR NONATDATECOSTSELLINGINTEREST31ALAA1N01548805SY7/1/20203504005041ALAA2Q545406JO7/1/20203204008051ALAA3A830660EG7/10/20203204008062ALAA17/15/20203504005073ALAA17/17/202035040050Sheet1Cell FormulasRangeFormulaB3:B7B3=COUNTIF($C$3:C3,C3)G7G7=IF($F7>0,"350",IF($F7>0,"",IF($F7<0," ","")))I3:I7I3=H3-G3


after
TEST (3).xlsxABCDEFGHI2AMOUNTCOUNTNAMEINSUR NONATDATECOSTSELLINGINTEREST301ALAA1N01548805SY7/1/202035040050401ALAA2Q545406JO7/1/202032040080501ALAA3A830660EG7/10/20203204008064002ALAA1N01548805SY7/15/20203504005074003ALAA1N01548805SY7/17/202035040050Sheet1Cell FormulasRangeFormulaB3:B7B3=COUNTIF($C$3:C3,C3)G7G7=IF($F7>0,"350",IF($F7>0,"",IF($F7<0," ","")))I3:I7I3=H3-G3


----------



## bebo021999 (Sep 23, 2022)

Does green font color come from manual color or conditioning formatting?


----------



## Abdo (Sep 23, 2022)

green font color come from manual color .


----------



## bebo021999 (Sep 23, 2022)

Try with VBA solution (Alt-F11 to open VBA window, insert/ module. copy below code the paste into:

```
Option Explicit
Sub test()
Dim lr&, cell As Range
lr = Cells(Rows.Count, "C").End(xlUp).Row
For Each cell In Range("A3:A" & lr)
    With cell.Offset(, 7)
        cell.Value = IIf(.Font.Color = 5287936, 0, .Value) ' 5287936: current green color found
    End With
Next
For Each cell In Range("D3:E" & lr)
    If IsEmpty(cell) Then
        cell.FormulaR1C1 = "=INDEX(R[-4]C:R[-1]C,MATCH(RC3,R[-4]C3:R[-1]C3,0))"
        cell.Value = cell.Value
    End If
Next
End Sub
```


----------



## Peter_SSs (Sep 23, 2022)

Especially if the data is large, this may have some benefit as it avoids looping through cells individually.


```
Sub CompleteData()
  With Range("A3:I" & Range("B" & Rows.Count).End(xlUp).Row)
    .Columns(1).Value = .Columns(8).Value
    On Error Resume Next
    With .Columns(4).Resize(, 2)
      .SpecialCells(xlBlanks).FormulaR1C1 = "=INDEX(R3C:R[-1]C,MATCH(RC3,R3C3:R[-1]C3,0))"
      .Value = .Value
    End With
    .Offset(-1).Resize(.Rows.Count + 1).AutoFilter Field:=8, Criteria1:=RGB(0, 176, 80), Operator:=xlFilterFontColor
    .Columns(1).SpecialCells(xlVisible).Value = 0
    On Error GoTo 0
    .Parent.AutoFilterMode = False
  End With
End Sub
```


----------



## Abdo (Sep 23, 2022)

two  options  are  great !
thanks  guys


----------



## Peter_SSs (Sep 23, 2022)

You're welcome. Glad we could help. Thanks for the follow-up.


----------



## Peter_SSs (Sep 23, 2022)

Oops, I forgot to mention. If you are actually using the solution that you have marked, you will need to alter the formula in the vba else if would not give correct values for the blank cells in this example

Abdo_1.xlsmBCDE12COUNTNAMEINSUR NONAT31ALAA1N01548805SY41ALAA2Q545406JO51ALAA3A830660EG61ALAA4XK71ALAA5YL81ALAA6ZM92ALAA1103ALAA1Sheet1 (8)Cell FormulasRangeFormulaB3:B10B3=COUNTIF($C$3:C3,C3)

I believe that the change required is


```
*cell.FormulaR1C1 = "=INDEX(R[-4]C:R[-1]C,MATCH(RC3,R[-4]C3:R[-1]C3,0))"*
*cell.FormulaR1C1 = "=INDEX(R3C:R[-1]C,MATCH(RC3,R3C3:R[-1]C3,0))"*
```


----------



## anbaz (Dec 30, 2022)

I want a VBA code similar, but my requirement is slightly different. I thought this thread is more suitable to ask for a solution.

Column: A is a List Value type where the cell values contains

1. Create
2. Modify
3. Activate
4. Inactivate.

Column: B is a List Value type where the cell values contains

New
Change Status
Add Channel
What I need is? If my user selects Col A = “Create” then in column B = New value should populate

If my user selects Col A = Modify, then in Column B Change Status & Add Channel should show in the dropdown list so that user can pick any one of the value from the list.



*Col=A*​*Col=B*​*Create or modify?*​*Type Of Change*​Create​New​Modify​​​


----------

