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

[Abdo]

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).xlsx
	A	B	C	D	E	F	G	H	I
2	AMOUNT	COUNT	NAME	INSUR NO	NAT	DATE	COST	SELLING	INTEREST
3		1	ALAA1	N01548805	SY	7/1/2020	350	400	50
4		1	ALAA2	Q545406	JO	7/1/2020	320	400	80
5		1	ALAA3	A830660	EG	7/10/2020	320	400	80
6		2	ALAA1			7/15/2020	350	400	50
7		3	ALAA1			7/17/2020	350	400	50
Sheet1

Cell Formulas
Range	Formula
B3:B7	B3	=COUNTIF($C$3:C3,C3)
G7	G7	=IF($F7>0,"350",IF($F7>0,"",IF($F7<0," ","")))
I3:I7	I3	=H3-G3

after

TEST (3).xlsx
	A	B	C	D	E	F	G	H	I
2	AMOUNT	COUNT	NAME	INSUR NO	NAT	DATE	COST	SELLING	INTEREST
3	0	1	ALAA1	N01548805	SY	7/1/2020	350	400	50
4	0	1	ALAA2	Q545406	JO	7/1/2020	320	400	80
5	0	1	ALAA3	A830660	EG	7/10/2020	320	400	80
6	400	2	ALAA1	N01548805	SY	7/15/2020	350	400	50
7	400	3	ALAA1	N01548805	SY	7/17/2020	350	400	50
Sheet1

Cell Formulas
Range	Formula
B3:B7	B3	=COUNTIF($C$3:C3,C3)
G7	G7	=IF($F7>0,"350",IF($F7>0,"",IF($F7<0," ","")))
I3:I7	I3	=H3-G3

 

[bebo021999]

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

 

[Abdo]

green font color come from manual color .

 

[bebo021999]

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]

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]

two options are great !
thanks guys

 

[Peter_SSs]

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

 

[Peter_SSs]

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.xlsm
	B	C	D	E
1
2	COUNT	NAME	INSUR NO	NAT
3	1	ALAA1	N01548805	SY
4	1	ALAA2	Q545406	JO
5	1	ALAA3	A830660	EG
6	1	ALAA4	X	K
7	1	ALAA5	Y	L
8	1	ALAA6	Z	M
9	2	ALAA1
10	3	ALAA1
Sheet1 (8)

Cell Formulas
Range	Formula
B3:B10	B3	=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]

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​
​
​